Description

Login
Back

Advanced · 18 min

Running totals and window frames

State which rows contribute to each window aggregate.

Sample database

CREATE TABLE groups (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, group_id INTEGER REFERENCES groups(id));
CREATE TABLE sessions (id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES members(id), topic TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes > 0), completed INTEGER NOT NULL CHECK(completed IN (0, 1)), day TEXT NOT NULL);
INSERT INTO groups VALUES (1, 'Morning'), (2, 'Evening');
INSERT INTO members VALUES (1, 'Ada', 'Oslo', 1), (2, 'Bo', 'Rome', 1), (3, 'Cy', NULL, 2), (4, 'Dee', 'Oslo', 2);
INSERT INTO sessions VALUES
  (1, 1, 'Travel', 20, 1, '2026-01-01'),
  (2, 1, 'Food', 10, 0, '2026-01-02'),
  (3, 2, 'Travel', 30, 1, '2026-01-01'),
  (4, 2, 'Music', 15, 1, '2026-01-03'),
  (5, 3, 'Music', 25, 0, '2026-01-02'),
  (6, 3, 'Travel', 15, 1, '2026-01-04');

Define a row frame

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW produces a cumulative total in the stated ordering. A unique order is important when several sessions share a day. An explicit frame avoids surprises from the default peer-aware frame.

SELECT id, SUM(minutes) OVER (ORDER BY day, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_minutes FROM sessions ORDER BY day, id;

Output

id | running_minutes
1 | 20
3 | 50
2 | 60
5 | 85
4 | 100
6 | 115

Restart a running total

PARTITION BY gives each member an independent running total. The window aggregate preserves every session row. GROUP BY would instead collapse multiple sessions into a single summary row.

SELECT member_id, id, SUM(minutes) OVER (PARTITION BY member_id ORDER BY day, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_minutes FROM sessions ORDER BY member_id, day, id;

Output

member_id | id | running_minutes
1 | 1 | 20
1 | 2 | 30
2 | 3 | 30
2 | 4 | 45
3 | 5 | 25
3 | 6 | 40

Put it into practice

  1. Read the sample tables and predict the result before running the query.
  2. Solve the task, compare the returned rows, and explain why the solution works.

Try it yourself

Code runs on this device. When you are signed in, drafts sync to your account.

Run queries on a fresh sample database. Table changes last for this run only; your query draft is saved separately. Results show column names followed by rows. NULL means a missing value.

Apply what you learned

Return each session and the running minutes within its member, ordered by member, day, and ID.

Show solution
SELECT member_id, id, SUM(minutes) OVER (PARTITION BY member_id ORDER BY day, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_minutes FROM sessions ORDER BY member_id, day, id;

Each member total starts independently, while the frame grows through that member sequence.

Practice