intermediate

Running Totals in SQL

By DataPlayArena Technical Review BoardUpdated 2026-07-208 min readRuntime Verified

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 ROW produces incorrect sums when ties exist.
  • Explicit Row Framing: Using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for 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 (...):

sql
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:

sql
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:

  1. PARTITION BY customer_id: Divides the rows into logical customer buckets.
  2. ORDER BY order_date: Sorts the rows chronologically within each customer's partition.
  3. 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 the ORDER BY column (a tie), RANGE lumps them together and outputs the sum of ALL tied rows on both rows!
sql
-- 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):

sql
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 BY in Window: Writing SUM(amount) OVER (PARTITION BY customer_id) without an ORDER BY clause 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:

  1. Frame Definition: ROWS frames data by physical row offsets (e.g. 5 rows before), while RANGE frames data by logical column value boundaries.
  2. Duplicate Value Behavior: When duplicate values exist in the ORDER BY column, RANGE treats all duplicates as a single logical entity, calculating the aggregate sum of all tied rows simultaneously. ROWS treats every record as a distinct physical entity and increments row by row.
  3. 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

Challenge: Cumulative Monthly Sales by Region

You have a regional_sales table with columns region, sale_month (date), and monthly_revenue. Write a query that computes a running total of revenue for each region across months, strictly using ROWS framing to prevent tie-related aggregation issues.

DPA

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.