Running Totals

A running total is SUM with an ORDER BY and the right frame; the frame, not the function, decides what accumulates.

SyntaxSUM(expr) OVER ([PARTITION BY ...] ORDER BY expr ... ROWS UNBOUNDED PRECEDING)

A running total is an ordinary SUM with an OVER clause that has an ORDER BY. What makes it cumulative is not the function but the frame: each row sees the rows from the start of the partition up to itself, so the sum grows row by row.

The frame is doing the work

When an OVER clause has an ORDER BY and no frame clause, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE compares values, so "current row" means the current row and all its peers — every row with the same ORDER BY value. Two ledger entries posted at the same instant are peers, and both get the total including each other. That is correct for a daily closing balance and wrong for a per-entry running balance.

ROWS counts positions instead, so each row adds exactly one. ROWS UNBOUNDED PRECEDING is the shorthand for ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; a lone frame_start always implies AND CURRENT ROW.

Ordering has to be total

If the ORDER BY does not uniquely order the rows, a ROWS running total is computed over an arbitrary sequence and can differ between runs. Add a tie-breaking unique column. And because windowing runs after GROUP BY, you can accumulate over aggregated rows in the same query rather than in a CTE.

Example

-- Account balance after each ledger entry.
SELECT
  l.account_id,
  l.entry_id,
  l.posted_at,
  l.amount_cents,
  -- ROWS UNBOUNDED PRECEDING is shorthand for
  -- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
  SUM(l.amount_cents) OVER (
    PARTITION BY l.account_id
    ORDER BY l.posted_at, l.entry_id   -- entry_id breaks same-timestamp ties
    ROWS UNBOUNDED PRECEDING
  ) AS balance_cents
FROM ledger_entries AS l
WHERE l.account_id = 42
ORDER BY l.posted_at, l.entry_id;

When to use it

Show an account balance after each ledger entry.
Build a year-to-date revenue column, one row per day.
Track cumulative units shipped against a purchase order.
Show how much of a budget remains after each spend.

More examples

RANGE default versus ROWS, on tied values

The first two columns are identical by definition — the second just writes out the default. Run this whenever a running total "jumps twice" on one day: it is almost always the RANGE default finding peers you did not know were there.

-- Two entries posted on the same day. RANGE treats them as peers and gives
-- both the day's closing figure; ROWS increments once per row.
SELECT posted_at, amount_cents,
       SUM(amount_cents) OVER (ORDER BY posted_at)                     AS range_default,
       SUM(amount_cents) OVER (ORDER BY posted_at
                               RANGE BETWEEN UNBOUNDED PRECEDING
                                         AND CURRENT ROW)              AS range_longhand,
       SUM(amount_cents) OVER (ORDER BY posted_at, entry_id
                               ROWS BETWEEN UNBOUNDED PRECEDING
                                        AND CURRENT ROW)               AS rows_strict
FROM ledger_entries
WHERE account_id = 42
ORDER BY posted_at, entry_id;

Accumulating over grouped rows

The nested SUM(SUM(...)) looks wrong but is exactly what the evaluation order permits: the inner SUM is the group aggregate, the outer one is the window over the grouped rows. Prefer the CTE version when the query is going to grow; prefer the nested form for a quick report.

-- Longhand: aggregate to one row per day, then accumulate in an outer query.
WITH daily AS (
  SELECT DATE(placed_at) AS d, SUM(total_cents) AS revenue_cents
  FROM orders
  WHERE placed_at >= '2026-01-01'
  GROUP BY DATE(placed_at)
)
SELECT d, revenue_cents,
       SUM(revenue_cents) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING) AS ytd_cents
FROM daily
ORDER BY d;

-- Shorthand: windowing runs after GROUP BY, so the window can sum the
-- aggregate directly. Same result, one query level.
SELECT DATE(placed_at) AS d,
       SUM(total_cents) AS revenue_cents,
       SUM(SUM(total_cents)) OVER (ORDER BY DATE(placed_at)
                                   ROWS UNBOUNDED PRECEDING) AS ytd_cents
FROM orders
WHERE placed_at >= '2026-01-01'
GROUP BY DATE(placed_at)
ORDER BY d;

Counting down instead of up

A reverse running total: the same idea with the frame opened forward instead of backward. Useful for burn-down charts and for "how much is left after this point" columns, without sorting the data in reverse.

-- How much of the commitment is still outstanding after each drawdown.
SELECT
  d.contract_id,
  d.drawn_at,
  d.amount_cents,
  -- Current row through the end of the partition.
  SUM(d.amount_cents) OVER (
    PARTITION BY d.contract_id
    ORDER BY d.drawn_at, d.id
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) AS remaining_cents
FROM drawdowns AS d
ORDER BY d.contract_id, d.drawn_at;

Discussion

Be the first to comment on this lesson.