PatternSQL
Running total
A cumulative sum beside each row — daily revenue becoming month-to-date — from one SUM ... OVER (ORDER BY) window.
Last updated
Pattern
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.