Cumulative Sums and Running Totals
You'll learn how to calculate running totals using window functions with precise frame specifications.
A running total (or cumulative sum) is a sum that grows as you move through your data, accumulating values row by row. Think of it like tracking your bank balance: each transaction adds to (or subtracts from) the running total of all previous transactions, including the current one.
The Core Pattern
To create a running total, you use SUM() with the OVER clause and specify a frame that includes all rows from the start of the partition up to the current row:
SELECT
transaction_date,
amount,
SUM(amount) OVER (
ORDER BY transaction_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM transactions;
Understanding the Frame Specification
UNBOUNDED PRECEDING: Start from the very first row in the ordered setCURRENT ROW: Include the current row in the calculationORDER BY: Critical! Defines the sequence for accumulation
This frame specification tells SQL: "For each row, sum all values from the beginning of the result set up through the current row."
Shorthand Syntax
Because running totals are so common, you can use a simpler default syntax:
SUM(amount) OVER (ORDER BY transaction_date)
When you specify ORDER BY without an explicit frame, SQL defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which typically behaves like the ROWS version for running totals.
Key Takeaway: Running totals use SUM() OVER with ORDER BY and a frame from UNBOUNDED PRECEDING to CURRENT ROW, accumulating values progressively through your ordered dataset.