SQL Running Total: SUM OVER ORDER BY Explained
· 7 min read
To calculate a running total in SQL, use SUM() as a window function with an ORDER BY inside OVER. For each row, it adds up the value on that row and every row before it in that order.
SELECT sale_date, revenue,
SUM(revenue) OVER (ORDER BY sale_date) AS running_total
FROM daily_revenue
ORDER BY sale_date;This works in Postgres, MySQL 8+, SQL Server 2012+, SQLite 3.25+, DuckDB, BigQuery and Snowflake. Below: the output, a cumulative sum per group, how to go from raw rows to a running total in one query, and two traps that give wrong numbers without an error.
A worked example
| sale_date | revenue |
|---|---|
| 2025-03-01 | 100 |
| 2025-03-02 | 120 |
| 2025-03-03 | 30 |
| 2025-03-04 | 80 |
| 2025-03-05 | 120 |
The query above returns:
| sale_date | revenue | running_total |
|---|---|---|
| 2025-03-01 | 100 | 100 |
| 2025-03-02 | 120 | 220 |
| 2025-03-03 | 30 | 250 |
| 2025-03-04 | 80 | 330 |
| 2025-03-05 | 120 | 450 |
Each running total is the previous one plus today's revenue: 100, then 100 + 120 = 220, then 220 + 30 = 250. The last row always equals the grand total, which makes a good sanity check.
How SUM OVER ORDER BY works
SUM(revenue) on its own would collapse the table into one number. Adding OVER (...) turns it into a window function: every row is kept, and each row gets a sum over a "window" of rows. With ORDER BY sale_date in the window, the default window is "from the first row up to this one". That default is what produces the cumulative sum.
The ORDER BY inside OVER controls the running order. The ORDER BY at the end of the query only controls how the result is displayed. You usually want both, set the same way.
Cumulative sum per group
Add PARTITION BY to restart the running total for each group. Here is the raw table the daily figures came from:
| sale_date | region | amount |
|---|---|---|
| 2025-03-01 | North | 100 |
| 2025-03-02 | North | 50 |
| 2025-03-02 | South | 70 |
| 2025-03-03 | South | 30 |
| 2025-03-04 | North | 80 |
| 2025-03-05 | South | 120 |
SELECT sale_date, region, amount,
SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS region_running_total
FROM sales
ORDER BY region, sale_date;| sale_date | region | amount | region_running_total |
|---|---|---|---|
| 2025-03-01 | North | 100 | 100 |
| 2025-03-02 | North | 50 | 150 |
| 2025-03-04 | North | 80 | 230 |
| 2025-03-02 | South | 70 | 70 |
| 2025-03-03 | South | 30 | 100 |
| 2025-03-05 | South | 120 | 220 |
North and South each have their own running total. Add the two final values and you get 450, the same grand total as before.
From raw rows to a running total in one query
Often you start with raw transactions and want a running total per day or per month. You can aggregate and run the window in the same query, because window functions are calculated after GROUP BY:
SELECT sale_date,
SUM(amount) AS revenue,
SUM(SUM(amount)) OVER (ORDER BY sale_date) AS running_total
FROM sales
GROUP BY sale_date
ORDER BY sale_date;| sale_date | revenue | running_total |
|---|---|---|
| 2025-03-01 | 100 | 100 |
| 2025-03-02 | 120 | 220 |
| 2025-03-03 | 30 | 250 |
| 2025-03-04 | 80 | 330 |
| 2025-03-05 | 120 | 450 |
SUM(SUM(amount)) looks odd. Read it from the inside: the inner SUM is the daily total from GROUP BY, the outer SUM ... OVER runs across those daily totals. If that is hard to read, do the grouping in a CTE first and run the window on the result. Both give the same answer.
For a monthly running total, group by month instead of by day. See how to extract the month from a date.
Running total as a share of the total
Divide the running total by the grand total to see how fast you reach 100%. SUM(revenue) OVER () with empty brackets is the grand total on every row:
SELECT sale_date, revenue,
SUM(revenue) OVER (ORDER BY sale_date) AS running_total,
ROUND(100.0 * SUM(revenue) OVER (ORDER BY sale_date) / SUM(revenue) OVER (), 1) AS pct_of_total
FROM daily_revenue
ORDER BY sale_date;| sale_date | revenue | running_total | pct_of_total |
|---|---|---|---|
| 2025-03-01 | 100 | 100 | 22.2 |
| 2025-03-02 | 120 | 220 | 48.9 |
| 2025-03-03 | 30 | 250 | 55.6 |
| 2025-03-04 | 80 | 330 | 73.3 |
| 2025-03-05 | 120 | 450 | 100 |
The 100.0 matters: with whole numbers on both sides, some databases do integer division and you get 0.
A moving total instead of a running total
A running total grows forever. A moving total only looks at the last few rows. Spell out the window frame:
SELECT sale_date, revenue,
SUM(revenue) OVER (ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS last_3_days
FROM daily_revenue
ORDER BY sale_date;| sale_date | revenue | last_3_days |
|---|---|---|
| 2025-03-01 | 100 | 100 |
| 2025-03-02 | 120 | 220 |
| 2025-03-03 | 30 | 250 |
| 2025-03-04 | 80 | 230 |
| 2025-03-05 | 120 | 230 |
On 4 March the window is 2, 3 and 4 March: 120 + 30 + 80 = 230.
Common mistakes
Leaving out ORDER BY (the AI mistake to watch for)
An AI assistant asked for a running total sometimes writes the window without an ORDER BY, or drops it while "simplifying" the query:
SELECT sale_date, revenue,
SUM(revenue) OVER () AS running_total
FROM daily_revenue
ORDER BY sale_date;| sale_date | revenue | running_total |
|---|---|---|
| 2025-03-01 | 100 | 450 |
| 2025-03-02 | 120 | 450 |
| 2025-03-03 | 30 | 450 |
| 2025-03-04 | 80 | 450 |
| 2025-03-05 | 120 | 450 |
Without an order there is nothing to "run" along, so every row gets the grand total.
How to spot it: a running total must go up (or stay flat) row by row, and only the last row should equal the grand total. If the first row already shows the grand total, the ORDER BY inside OVER is missing.
Running over raw rows with duplicate dates
Run the window directly on the raw sales table, where 2 March has two rows:
SELECT sale_date, region, amount,
SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
ORDER BY sale_date, region;| sale_date | region | amount | running_total |
|---|---|---|---|
| 2025-03-01 | North | 100 | 100 |
| 2025-03-02 | North | 50 | 220 |
| 2025-03-02 | South | 70 | 220 |
| 2025-03-03 | South | 30 | 250 |
| 2025-03-04 | North | 80 | 330 |
| 2025-03-05 | South | 120 | 450 |
Both 2 March rows show 220. The default window frame is RANGE, which treats rows with the same ORDER BY value as peers and includes all of them at once. If you add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW you get 150 then 220, but which row comes first is arbitrary unless you add a tie-breaker. Either way, the cleanest fix is to aggregate to one row per date first, as in the one-query example above.
Partitioning by the wrong column
PARTITION BY sale_date restarts the total every day, so the "running" total is just the daily total. Partition by the group you want separate totals for (region, product), never by the column you are running along.
FAQ
How do I calculate a running total without window functions?
Join the table to itself on b.sale_date <= a.sale_date and sum b.revenue per a.sale_date. It gives the same result but gets slow on big tables. Every modern database supports window functions, so use them.
What is the difference between a running total and a cumulative sum?
None. They are two names for the same calculation.
How do I reset the running total every year or month?
Add the period to PARTITION BY, for example PARTITION BY EXTRACT(YEAR FROM sale_date) ORDER BY sale_date. The total starts again at each new year.
Can I make a running count or running average?
Yes. Swap SUM for COUNT or AVG and keep the same OVER (ORDER BY ...).
Window functions also handle ranking: see RANK OVER PARTITION BY.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Revenue so far this year” →