Window Functions

Running Total

Accumulating Over Order

A window aggregate can show the cumulative value at each row without collapsing rows.

Program

Play the query to compute a running payment total from a selectable start day.

running_total.sql 
CREATE TABLE payments (day INTEGER, amount INTEGER);
INSERT INTO payments VALUES (1, 5), (2, 7), (3, 4);
WITH params(start_day) AS (VALUES ()) SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM payments WHERE day >= (SELECT start_day FROM params) ORDER BY day;

CREATE TABLE payments (day INTEGER, amount INTEGER);
INSERT INTO payments VALUES (1, 5), (2, 7), (3, 4);
WITH params(start_day) AS (VALUES ()) SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM payments WHERE day >= (SELECT start_day FROM params) ORDER BY day;

CREATE TABLE payments (day INTEGER, amount INTEGER);
INSERT INTO payments VALUES (1, 5), (2, 7), (3, 4);
WITH params(start_day) AS (VALUES ()) SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM payments WHERE day >= (SELECT start_day FROM params) ORDER BY day;
window aggregate `SUM(amount) OVER (...)` keeps one output row per input row.
running total `ORDER BY day` makes each row include the previous days in order.
filter first The selectable start day changes which rows enter the window.