Running Totals (ROWS)
Overview
A standard SUM() OVER(PARTITION BY dept) assigns the exact same grand total to every row in the department. But what if you want a 'Running Total' (Cumulative Sum) that builds up row-by-row chronologically? To do this, you add an ORDER BY inside the SUM() OVER() clause. This fundamentally changes the math engine, triggering a 'Frame Clause' (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), which tells SQL to only sum data up to the current point in time.
Syntax
-- The Magic of the Frame Clause
SELECT
date,
daily_sales,
-- 1. Standard Window Sum (Everyone gets the Grand Total: 1000)
SUM(daily_sales) OVER () AS grand_total,
-- 2. Running Total! (Because of the ORDER BY)
-- Row 1: 10
-- Row 2: 10 + 20 = 30
-- Row 3: 10 + 20 + 30 = 60
SUM(daily_sales) OVER (ORDER BY date ASC) AS cumulative_sales
FROM sales;Common Pitfalls
- Accidental Frame Collisions via ties. If you
ORDER BY dateto build a running total, and you have two rows with the exact same date, the default SQL behavior isRANGE(notROWS). It will group those two ties together and assign them the exact same merged total. To force a strict row-by-row increment even on ties, you must manually declare the frame:ORDER BY date ASC ROWS UNBOUNDED PRECEDING. - Performance on massive datasets. Calculating a rolling sum across 50 million rows is extremely CPU intensive, as the window frame expands dynamically for every single row evaluated.
Interview Questions
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING do when attached to a SUM() window function?It calculates a 3-row moving average/sum. It restricts the math exclusively to the previous row, the current row, and the next row, discarding everything else. This is heavily used in stock market moving averages.
Real-World Example
Calculating a 7-Day Rolling Moving Average for daily active users.
SELECT
date,
dau,
-- Averages the previous 6 days + the current day!
AVG(dau) OVER (
ORDER BY date ASC
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7_day_avg
FROM daily_metrics;Check Your Knowledge
Test your understanding of Running Totals (ROWS) with these quick questions.