Lessons · SQL · a total that grows down the rows

A running total

SUM(amount) OVER (ORDER BY placed) adds up every row up to and including the current one. The ORDER BY inside the window is what makes it accumulate.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

Cumulative revenue across a month, stock level after each movement, a balance after each transaction: the running total is the chart people point at.

How to think about it

Order the window by time, and partition when each group should restart (per customer, per account). Break ties deliberately with a second sort key, or two rows on the same day get the same total.

Worked example

SELECT placed, amount, SUM(amount) OVER (ORDER BY placed, id) AS running

Up to and including this row, in date order; id breaks ties.

FROM orders;

Every row kept.

SELECT placed, amount, SUM(amount) OVER () AS grand FROM orders;

Drop the ORDER BY and every row gets the same grand total: the usual mistake.

Your turn

Running total per customer.

SUM(amount) OVER (PARTITION BY customer_id  placed) AS running

The trap

With ORDER BY on a column that has ties and no tie-breaker, rows sharing a value get the same running total, because the default frame includes all peers.

Practise a total that grows down the rows on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.