Pathubs SQL Curriculum • Module 17

SQL Running Totals (Cumulative Sums)

Master cumulative running calculations in SQL: understand how window aggregates accumulate preceding values row-by-row, compare Running Totals against Grand Totals and standard SUMs, partition cumulative metrics, and practice in a live interactive SQL lab.

⏱️ Estimated Time:30 Minutes

🎯 Level:Intermediate Financial & Analytics SQL

📊 Track:Data Analytics & Revenue Operations

✨ Mode:Live Deterministic Practice Lab

Introduction: Daily Revenue Tracking

Suppose a company records the following daily sales:

Date	Daily Sales	Cumulative Total Up to That Day
Jan 1	$100	$100
Jan 2	$200	$300 ($100 + $200)
Jan 3	$150	$450 ($100 + $200 + $150)
Jan 4	$300	$750 ($100 + $200 + $150 + $300)

How much has the business sold in total up to each specific day? This progressive calculation is called a Running Total (or Cumulative Total).

What Is a Running Total? (The Core Mental Model)

Core Mental Model

Running Total = Current Row Value + All Relevant Preceding Row Values in the ordered sequence.

Row 1: 100                  ➔ 100
Row 2: 100 + 200            ➔ 300
Row 3: 100 + 200 + 150      ➔ 450
Row 4: 100 + 200 + 150 + 300 ➔ 750

The SQL Running Total Pattern

In standard SQL, running totals are built by combining SUM() with an OVER(ORDER BY ...) window specification:

SELECT
    sale_date,
    sales,
    SUM(sales) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM daily_sales;

SUM(sales)

The column value being accumulated.

OVER (...)

Converts aggregate into a window calculation.

ORDER BY date

Defines chronological sequence of rows.

ROWS BETWEEN...

Start from the very beginning up to current row.

Running Total vs. Normal SUM()

A standard SUM(sales) query collapses all input rows into a single scalar summary number. In contrast, the windowed running total preserves every individual granular daily row while appending the cumulative calculation:

Query Style	Syntax	Output Row Count	Granular Details
Standard Aggregate	`SELECT SUM(sales) FROM daily_sales;`	1 Row ($750)	Lost / Collapsed
Window Running Total	`SELECT date, sales, SUM(sales) OVER (ORDER BY date...)`	All 4 Rows Preserved	Intact + Progress Tracked

Running Total vs. Grand Total

This is one of the most common beginner confusions:

Date	Daily Sales	Grand Total: `SUM(sales) OVER ()`	Running Total: `SUM(sales) OVER (ORDER BY date)`
Jan 1	$100	$750 (Static)	$100
Jan 2	$200	$750 (Static)	$300
Jan 3	$150	$750 (Static)	$450
Jan 4	$300	$750 (Static)	$750

Why ORDER BY Is Critical

Crucial Rule: Sequence Defines Progress

Relational tables have no inherent natural order on disk. The ORDER BY clause establishes what has chronologically or logically occurred before the current row.

Running Totals With PARTITION BY

Adding PARTITION BY region instructs SQL to restart the running total accumulation independently for each geographic region:

SELECT
    region,
    sale_date,
    sales,
    SUM(sales) OVER (
        PARTITION BY region
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS regional_running_total
FROM regional_sales;

Region	Date	Sales	Regional Running Total	Why?
West	Jan 1	$100	$100	West baseline
West	Jan 2	$200	$300	West accumulated ($100 + $200)
East	Jan 1	$300	$300 (Restarted!)	East starts fresh
East	Jan 2	$100	$400	East accumulated ($300 + $100)

Real-World Data Analytics Use Cases

Year-to-Date (YTD) Revenue: Tracking whether pacing meets quarterly targets.
Bank Account Ledger: Calculating real-time account balances from deposits and withdrawals.
Customer Lifetime Value (LTV): Tracking a user's cumulative spend progression after signup.
Inventory Depletion: Monitoring remaining warehouse stock as daily customer orders ship out.

Negative Numbers: Running Totals Can Decrease

Running totals are not strictly upward-sloping. In financial accounting, debits decrease the cumulative balance:

+$500 (Deposit)    ➔ $500
-$100 (Withdrawal) ➔ $400
+$300 (Deposit)    ➔ $700
-$50  (Fee)        ➔ $650

Duplicate Ordering Values & Explicit Window Frames

If two rows share the identical date (e.g. two sales on Jan 1), default RANGE BETWEEN can sum them together at once. Specifying ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW guarantees strict, deterministic row-by-row accumulation.

Common Running Total Mistakes to Avoid

1. Omitting ORDER BY

Accidentally writing SUM(sales) OVER (), which produces the static Grand Total on every row.

2. Forgetting PARTITION BY

Accumulating regional sales or customer orders across all accounts without resetting per group.

3. Using GROUP BY

Using GROUP BY when you intended to keep individual transactions visible.

4. Assuming Physical Row Order

Trusting that the database stores rows in chronological order without an explicit timestamp order.

Practical Step-by-Step Exercises

Exercise 1: Customer Cumulative Spending

SELECT
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS customer_cumulative_spend
FROM orders;

⚡ Live Interactive SQL Running Totals Practice Lab

Execute deterministic SQL queries, edit live dataset values, compare Grand Total vs Running Total, and solve analytics challenges

SQL Query Editor (Live Deterministic Engine):

Calculated Result Table:

SQL Running Total Best Practices

Always Specify Meaningful ORDER BY: Prevents non-deterministic accumulation order.
Use Explicit Window Frames: Writing ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW prevents peer-tie grouping surprises with identical timestamps.
Partition When Resetting Entities: Add PARTITION BY account_id or customer_id to avoid cross-pollinating account balances.

What You Should Know Now: Checklist

✓Running Total Definition: Current row value + all preceding row values in ordered sequence.
✓Syntax: SUM(col) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
✓Running vs Grand Total: Adding ORDER BY turns a static Grand Total into a cumulative sequence.
✓PARTITION BY: Restarts the cumulative running total independently for each group.

TEST YOUR KNOWLEDGE

Knowledge Assessment: SQL Running Totals

Test your understanding of cumulative window aggregation, ORDER BY necessity, window frame boundaries, and PARTITION BY restarts.

Question 1 of 8Current Score: 0 / 0

What is the core definition of a running total in SQL?