Window functions Updated November 2026

Running total

Q: How do I calculate a cumulative sum in SQL?

SUM(column) OVER (ORDER BY ordering_column). Add PARTITION BY to restart per group. Supported by SQLite 3.25+, PostgreSQL, MySQL 8 and SQL Server.

Q: How do I do a running total without window functions?

Join the table to itself (or use a correlated subquery) summing rows with a date less than or equal to the current row. It is correct but slow; use window functions where available.

SUM(amount) OVER (ORDER BY order_date) returns, for each row, the sum of amount for that row and every earlier row: a cumulative total. Add PARTITION BY customer_id to restart the total per customer, and add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW if the ordering column can have duplicates.

Syntax

SUM(column) OVER ([PARTITION BY cols] ORDER BY cols [ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW])

Examples

SELECT id, order_date, amount,
       SUM(amount) OVER (ORDER BY order_date, id) AS running_total
FROM orders;

Result: id | order_date | amount | running_total 1 | 2025-01-03 | 100 | 100 2 | 2025-01-05 | 50 | 150 3 | 2025-01-09 | 75 | 225

Adding id to ORDER BY makes the order unique so ties do not share a total.

SELECT customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_spent
FROM orders;

Result: A separate running total per customer

SELECT id, amount,
       AVG(amount) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM orders;

Result: Three-row moving average

The same frame clause gives moving averages and sums.

Common mistakes

Omitting ORDER BY in OVER returns the grand total on every row instead of a running total.
Ties in the ORDER BY column show the same cumulative value for all tied rows, because the default frame is RANGE; use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW or make the order unique.
Trying a running total with a correlated subquery (SELECT SUM(...) WHERE date <= outer.date); it works but is far slower than the window form on large tables.

Practice Running total

These drills check your answer on live data, free, no account needed.

Running revenue totalFor every paid order, return the order id and the running total of paid revenue, ordered by order date and then by id.

Questions

How do I calculate a cumulative sum in SQL?