Running Totals
SQL · Extra Concepts
Theory
A running total (cumulative sum) adds each row's value to the total of all rows before it, in a chosen order. For daily sales of ₹1,200, ₹850 and ₹2,300, the running totals are ₹1,200, ₹2,050 and ₹4,350. It answers questions like 'how much revenue had we made by each day of the month?'.
In modern SQL you write it with a window function: SUM(amount) OVER (ORDER BY order_date). The ORDER BY inside OVER defines the sequence, and every row keeps its own detail while gaining a cumulative column; nothing is collapsed like with GROUP BY. Add PARTITION BY customer_id to restart the total for each customer. One subtle point: when ORDER BY is present, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats rows with the same order value as peers and gives them the same total. If you need strictly row-by-row totals, order by a unique column or write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly.
Why we use it
Cumulative figures show progress over time: revenue to date, stock levels, account balances, progress toward a target. A window function computes them in one pass without self-joins.
When to use it
Use SUM() OVER (ORDER BY ...) for to-date totals, balances and cumulative counts. Add PARTITION BY to restart per group. Use a moving average or a fixed ROWS frame when you need a rolling window rather than everything so far.
How it works
- Choose the order: ORDER BY order_date
- Restart per group? Yes: Add PARTITION BY customer_id. No: One total for all rows.
- For each row, add its amount to the total of rows before it
- Ties in the ORDER BY value? Yes: Use a ROWS frame or tie-breaker. No: Default frame is fine.
- Each row shows its own cumulative total
Syntax
SUM(column) OVER (ORDER BY col)Example
SELECT name, salary,
SUM(salary) OVER (ORDER BY id) AS running_total
FROM employees;Key points
- A direct application of the SUM() window function
- Default frame ends at the current row, but ties in ORDER BY share the same total
- Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for strict row-by-row totals
- PARTITION BY restarts the total for each group (e.g. per customer)
- Unlike GROUP BY, every detail row is kept