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)
Current Row Value + All Relevant Preceding Row Values in the ordered sequence.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:
sale_date,
sales,
SUM(sales) OVER (
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM daily_sales;
The column value being accumulated.
Converts aggregate into a window calculation.
Defines chronological sequence of rows.
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
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:
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:
-$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
Accidentally writing SUM(sales) OVER (), which produces the static Grand Total on every row.
Accumulating regional sales or customer orders across all accounts without resetting per group.
Using GROUP BY when you intended to keep individual transactions visible.
Trusting that the database stores rows in chronological order without an explicit timestamp order.
Practical Step-by-Step Exercises
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;
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 ROWprevents peer-tie grouping surprises with identical timestamps. - Partition When Resetting Entities: Add
PARTITION BY account_idorcustomer_idto 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 BYturns a static Grand Total into a cumulative sequence. - ✓PARTITION BY: Restarts the cumulative running total independently for each group.