Running total
A cumulative sum beside each row — daily revenue becoming month-to-date — from one SUM ... OVER (ORDER BY) window.
Last updated
SELECT day,
amount,
SUM(amount) OVER (ORDER BY day) AS running_total
FROM daily_sales
ORDER BY day;Why it’s written this way
SUM with an ORDER BY inside OVER stops being a plain aggregate and becomes cumulative: each row's value is the sum of everything up to and including it. The table keeps all its rows; the total grows down the column.
Add PARTITION BY month before the ORDER BY and the total resets at each month boundary — that one clause is the difference between year-to-date and month-to-date.
Edge cases to know
- →Duplicate day values make the default window (RANGE) give both rows the same total — add a unique tiebreaker to ORDER BY, or specify ROWS UNBOUNDED PRECEDING, for row-by-row accumulation.
- →The outer ORDER BY is still required for display — the window's internal ordering doesn't sort the result set.
- →NULL amounts are skipped by SUM (they don't zero the total) — COALESCE first if a NULL should mean 0.
Part by part
- SUM(amount) OVER (ORDER BY day)
- An aggregate used as a window function. The OVER clause is what stops it collapsing the result to a single row and makes it accumulate across them instead.
- ORDER BY day
- Inside the window, this defines the accumulation order — each row's total includes every row up to and including itself in this order.
- AS running_total
- Names the computed column. Without an alias most databases produce an unusable generated name for it.
- ORDER BY day;
- The outer sort. It is separate from the window's own ordering, and omitting it can return the rows in a different order than they were accumulated.
Common mistakes
SELECT day, amount, SUM(amount) FROM daily_sales GROUP BY day, amount;Without OVER this is an ordinary aggregate, so it returns the sum for each group rather than a running one. The output looks reasonable and is simply the original amounts back again.
SUM(amount) OVER ()An empty OVER clause sums the whole result set and puts the same grand total on every row. It is a useful thing to want occasionally, and never what someone writing a running total intends.
SUM(amount) OVER (ORDER BY day) with duplicate days in the dataThe default frame includes all peer rows tied at the same ORDER BY value, so two rows on the same day both show the total including both. Add a unique tiebreak, or state the frame explicitly with ROWS.
Questions
Why does my running total jump on days with several rows?
Because the default window frame is RANGE, which includes every row sharing the current ORDER BY value. All rows for that day therefore show the same cumulative figure. Switching to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW makes it strictly row-by-row.
How do I get a running total per customer?
Add a partition: SUM(amount) OVER (PARTITION BY customer_id ORDER BY day). The accumulation then restarts for each customer instead of running through the whole table.
Do I need the outer ORDER BY as well?
Yes, if the row order matters to you. The window's ORDER BY controls how the total accumulates, not how results are returned — without an outer sort the database is free to hand back rows in any order.
Can I compute a moving average the same way?
Yes, with an explicit frame: AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) gives a seven-day trailing average. The frame clause is what turns a cumulative window into a sliding one.