SUM() OVER (ORDER BY …)

Running totals

Lesson 9 of 20 · 10 min · Module 3: Window functions in depth

"How much have we earned so far this quarter?" is a running total: each row adds its value to everything before it. Window functions calculate it while keeping every row visible.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Calculate cumulative sums
Choose a deterministic window order
Combine a per-order CTE with a window

The idea

With ORDER BY inside OVER, an aggregate runs over the rows up to the current one. Add a unique tie-breaker such as the id, so rows on the same date are added in a defined order. Totals per order come from a CTE.

SELECT id, stock, SUM(stock) OVER (ORDER BY id) AS running_stock
FROM products;

01ORDER BY changes the window

SUM(x) OVER () is the grand total. SUM(x) OVER (ORDER BY day) is the total from the first row up to the current one, which is a running total.

02Ties need a tie-breaker

Orders 12 and 13 share a date. With ORDER BY ordered_at alone, SQL treats rows with equal values as peers and adds them together in one step. Adding id gives every row its own step.

03Windows run last

Window functions see the result after WHERE and GROUP BY. That is why the per-order totals are computed first, then accumulated.

SELECT id, SUM(id) OVER (ORDER BY id) FROM products LIMIT 3;

Output

1 | 1
2 | 3
3 | 6

Your turn

For each paid or shipped order, return id, ordered_at, its total_cents, and running_cents: the total of this and all earlier orders by date, then id. Sort the same way.

What the checks look for

Adds each order to the running total in date order

Try it in the editor →

Take this with you

Running totals are SUM with an ordered window.

Go a little further

Restart the running total each month with PARTITION BY strftime('%Y-%m', ordered_at).