Calculate a Running Total in SQL: Window Function vs Self-Join
Two approaches to cumulative sums in SQL — the modern SUM() OVER() window function and the legacy self-join. With MySQL, PostgreSQL, and BigQuery examples.
What Is a Running Total?
A running total (cumulative sum) adds each row's value to the sum of all previous rows. Think: bank balance after each transaction, or cumulative revenue day by day.
Modern Approach: SUM() OVER()
SELECT date, amount, SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS running_total FROM transactions;
This works in MySQL 8.0+, PostgreSQL, BigQuery, Snowflake, and SQL Server 2012+.
Partitioned Running Total (per group)
SELECT customer_id, date, amount, SUM(amount) OVER (PARTITION BY customer_id ORDER BY date) AS customer_running_total FROM transactions;
Legacy Approach: Self-Join (MySQL 5.7)
SELECT t1.date, t1.amount, SUM(t2.amount) AS running_total FROM transactions t1 JOIN transactions t2 ON t2.date <= t1.date GROUP BY t1.date, t1.amount ORDER BY t1.date;
Performance Note
The window function approach is O(n log n) with proper indexing. The self-join is O(n²) — avoid it on tables with more than 10K rows.