Running Totals in SQL
Overview
A running total (or cumulative sum) calculates the progressive sum of a numeric column across sequential rows within a dataset or category partition. In modern SQL, running totals are computed using the SUM() OVER (...) Window Function. Understanding how the window framing clause (ROWS BETWEEN ...) controls row evaluation is essential for preventing silent calculation bugs caused by duplicate values.
What You Will Learn
In this lesson, you will master:
- Window Function Mechanics: Calculating cumulative sums without self-joins or correlated subqueries.
- Partitioning: Resetting running totals per customer, department, or calendar year.
- The Default Window Frame Trap: Why
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWproduces incorrect sums when ties exist. - Explicit Row Framing: Using
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWfor strict sequential summation. - Moving Averages & Rolling Windows: Calculating 7-day, 30-day, and N-period trailing metrics.
Anatomy of a Running Total Query
The basic syntax for a running total utilizes SUM(column) OVER (...):
SELECT
transaction_id,
transaction_date,
amount,
-- Cumulative running total across the entire table
SUM(amount) OVER (
ORDER BY transaction_date, transaction_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM bank_transactions;
Partitioned Running Totals (Resetting per Category)
To reset the cumulative sum for each customer or category, add the PARTITION BY clause:
SELECT
customer_id,
order_id,
order_date,
order_total,
-- Running total calculated independently for EACH customer
SUM(order_total) OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS customer_cumulative_spend
FROM orders
ORDER BY customer_id, order_date;
Execution Breakdown:
PARTITION BY customer_id: Divides the rows into logical customer buckets.ORDER BY order_date: Sorts the rows chronologically within each customer's partition.ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Sums from the very first row of that customer's partition up to the current row being evaluated.
The Critical Default Window Trap: RANGE vs ROWS
The Most Common SQL Window Function Bug
If you write SUM(val) OVER (ORDER BY col) without an explicit framing clause, ANSI SQL automatically defaults to:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (NOT ROWS!).
Why Does This Matter?
ROWS: Evaluates physical row positions. Each row adds its own value sequentially.RANGE: Evaluates logical value ranges. If two rows have identical values in theORDER BYcolumn (a tie),RANGElumps them together and outputs the sum of ALL tied rows on both rows!
-- Sample Table:
-- Date | Amount
-- 2026-07-01 | 100
-- 2026-07-02 | 50 <-- Tie on date
-- 2026-07-02 | 50 <-- Tie on date
-- DANGEROUS DEFAULT: SUM(amount) OVER (ORDER BY Date)
-- Evaluates as: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- Output:
-- 2026-07-01 | 100 | Running Total: 100
-- 2026-07-02 | 50 | Running Total: 200 (INCORRECT! Added both rows simultaneously)
-- 2026-07-02 | 50 | Running Total: 200 (INCORRECT!)
-- SAFE & PRECISE: Explicit ROWS framing
-- SUM(amount) OVER (ORDER BY Date, Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- Output:
-- 2026-07-01 | 100 | Running Total: 100
-- 2026-07-02 | 50 | Running Total: 150 (CORRECT)
-- 2026-07-02 | 50 | Running Total: 200 (CORRECT)
Rule of Thumb: Always explicitly specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when computing running totals.
Rolling Windows & Moving Averages
You can adjust the window frame to calculate moving metrics (e.g., rolling 3-period sum or 7-row moving average):
SELECT
daily_date,
daily_revenue,
-- 7-Day Moving Average (Current row + preceding 6 rows)
AVG(daily_revenue) OVER (
ORDER BY daily_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7day,
-- 3-Period Rolling Total
SUM(daily_revenue) OVER (
ORDER BY daily_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_3period_total
FROM daily_sales;
Common Mistakes & Gotchas
- Omitting
ORDER BYin Window: WritingSUM(amount) OVER (PARTITION BY customer_id)without anORDER BYclause does not calculate a running total! It calculates the grand total across the entire partition and repeats that static total on every row. - Using Correlated Subqueries Instead of Window Functions: In legacy SQL, developers wrote running totals with self-joins (
SELECT (SELECT SUM(...) FROM t t2 WHERE t2.id <= t1.id)). This is an $O(N^2)$ operation that completely freezes tables with more than 10,000 rows. Window functions run in linear $O(N \log N)$ time.
Interview Perspective
SQL Interview Question: What is the difference between RANGE and ROWS in an analytical window function?
Key Talking Points for Candidates:
- Frame Definition:
ROWSframes data by physical row offsets (e.g. 5 rows before), whileRANGEframes data by logical column value boundaries. - Duplicate Value Behavior: When duplicate values exist in the
ORDER BYcolumn,RANGEtreats all duplicates as a single logical entity, calculating the aggregate sum of all tied rows simultaneously.ROWStreats every record as a distinct physical entity and increments row by row. - Default Behavior: If omitted, standard SQL defaults to
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is often slower and produces unexpected duplicate totals on ties.
Interactive Practice Challenge
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.