Running totals with SUM OVER in SQL - Deep Dive

Overview - Running totals with SUM OVER

What is it?

Running totals with SUM OVER is a way to calculate a cumulative sum of values in a column as you move down the rows of a table. Instead of adding all values at once, it adds them step-by-step, showing the total up to each row. This is done using the SUM function combined with the OVER clause in SQL. It helps track how values accumulate over time or order.

Why it matters

Without running totals, you would have to write complex queries or do calculations outside the database to see how values add up over time. This makes it hard to analyze trends or progress in data like sales, expenses, or scores. Running totals let you get this insight quickly and efficiently inside the database, saving time and reducing errors.

Where it fits

Before learning running totals, you should understand basic SQL SELECT queries, aggregation functions like SUM, and the concept of ordering rows. After this, you can explore more advanced window functions, ranking, and moving averages to analyze data trends deeply.

Mental Model

Core Idea

Running totals with SUM OVER add up values step-by-step across rows, showing the cumulative sum at each point without collapsing the data into a single total.

Think of it like...

Imagine filling a jar with coins one by one and writing down the total amount after each coin is added. Each note shows how much money is in the jar so far, just like running totals show the sum up to each row.

┌─────────────┬─────────┬───────────────┐
│ Row Number  │ Amount  │ Running Total │
├─────────────┼─────────┼───────────────┤
│ 1           │ 10      │ 10            │
│ 2           │ 5       │ 15            │
│ 3           │ 8       │ 23            │
│ 4           │ 7       │ 30            │
└─────────────┴─────────┴───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding basic SUM aggregation

Concept: Learn how the SUM function adds all values in a column to produce a single total.

Consider a sales table with a column 'amount'. Using SELECT SUM(amount) FROM sales; adds all sales amounts together and returns one total number.

Result

A single number representing the total sum of all amounts in the table.

Understanding how SUM works on a whole column is essential before learning how to calculate running totals step-by-step.

2

Foundation

Ordering rows for meaningful calculations

Concept: Learn that the order of rows matters when calculating running totals because the sum builds as you move through the data.

If you want a running total by date, you must order rows by date. For example, SELECT date, amount FROM sales ORDER BY date; shows sales in time order.

Result

Rows displayed in the order you want to accumulate values, such as chronological order.

Knowing how to order rows ensures running totals reflect the correct sequence, like adding coins in the right order.

3

Intermediate

Using SUM() OVER() for running totals

🤔Before reading on: do you think SUM() OVER() requires grouping rows like GROUP BY? Commit to your answer.

Concept: Introduce the window function SUM() OVER() that calculates running totals without grouping rows.

The query SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total FROM sales; calculates a running total by adding amounts in date order for each row.

Result

A table showing each sale with its amount and the cumulative sum up to that date.

Understanding that SUM() OVER() works as a window function lets you keep all rows while calculating cumulative sums.

4

Intermediate

Partitioning running totals by groups

🤔Before reading on: do you think running totals can reset for each category automatically? Commit to your answer.

Concept: Learn how to calculate running totals separately for different groups using PARTITION BY inside OVER().

For sales by region, use SELECT region, date, amount, SUM(amount) OVER (PARTITION BY region ORDER BY date) AS running_total FROM sales; This resets running totals for each region.

Result

Running totals calculated independently for each region, starting fresh at each new group.

Knowing how to partition data allows running totals to reflect group-specific progress, not just overall totals.

5

Intermediate

Controlling window frame for precise sums

🤔Before reading on: do you think the default window frame includes all rows or just up to the current row? Commit to your answer.

Concept: Understand how to define the exact range of rows to include in the running total using ROWS BETWEEN in the OVER clause.

By default, SUM() OVER (ORDER BY date) uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. You can specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for exact control.

Result

Running totals that precisely sum all rows from the start up to the current row, avoiding unexpected inclusions.

Controlling the window frame prevents subtle bugs and ensures running totals behave exactly as intended.

6

Advanced

Performance considerations with large datasets

🤔Before reading on: do you think running totals with SUM OVER scale well on very large tables? Commit to your answer.

Concept: Explore how running totals perform on big data and how indexes and query plans affect speed.

Running totals require sorting and scanning rows. On large tables, adding indexes on the ORDER BY columns and careful query tuning improves performance. Some databases optimize window functions internally.

Result

Faster queries and smoother running total calculations even with millions of rows.

Knowing performance factors helps you write efficient queries and avoid slow reports in real-world systems.

7

Expert

Advanced uses and edge cases of SUM OVER

🤔Before reading on: do you think running totals can handle gaps or duplicates in ordering columns without issues? Commit to your answer.

Concept: Learn about tricky cases like duplicate order values, NULLs, and how different databases handle them in running totals.

If ORDER BY column has duplicates, running totals include all tied rows together by default. Using ROWS BETWEEN can change this. NULLs in ordering can cause unexpected results. Different SQL engines may vary in behavior.

Result

Awareness of subtle behaviors that can cause incorrect running totals if not handled carefully.

Understanding edge cases prevents bugs and ensures your running totals are accurate in all scenarios.

Under the Hood

SUM OVER works by defining a 'window' of rows for each row in the result set. The database engine sorts rows by the ORDER BY clause inside OVER, then for each row, it sums values from the start of the window up to the current row. This happens without collapsing rows, unlike GROUP BY. Internally, the engine uses efficient algorithms to avoid recalculating sums from scratch for each row.

Why designed this way?

Window functions were designed to allow calculations across sets of rows related to the current row without losing row-level detail. This was a big improvement over older methods that required self-joins or subqueries, which were slower and more complex. The design balances expressiveness and performance.

┌───────────────┐
│   Table Rows  │
│ (unsorted)   │
└──────┬────────┘
       │ ORDER BY
       ▼
┌───────────────┐
│ Sorted Rows   │
│ (by date)     │
└──────┬────────┘
       │ For each row:
       ▼
┌─────────────────────────────┐
│ SUM values from first row to │
│ current row (running total) │
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does SUM() OVER() group rows like GROUP BY? Commit to yes or no.

Common Belief: SUM() OVER() works like GROUP BY and collapses rows into one total per group.

Reality: SUM() OVER() keeps all rows and calculates a running total for each row without grouping or collapsing.

Why it matters: Believing this causes confusion and incorrect query design, leading to loss of row details needed for analysis.

Quick: Does the order of rows affect running totals? Commit to yes or no.

Common Belief: Running totals are the same regardless of row order.

Reality: Running totals depend entirely on the ORDER BY clause; changing order changes the cumulative sums.

Why it matters: Ignoring order leads to wrong totals and misleading insights, especially in time series data.

Quick: Can running totals reset automatically for groups without PARTITION BY? Commit to yes or no.

Common Belief: Running totals reset for each group automatically without extra syntax.

Reality: You must use PARTITION BY to reset running totals per group; otherwise, totals continue across all rows.

Why it matters: Missing PARTITION BY causes totals to mix groups, producing incorrect cumulative sums.

Quick: Do all SQL databases handle duplicates in ORDER BY the same way for running totals? Commit to yes or no.

Common Belief: All databases treat duplicate ORDER BY values identically in running totals.

Reality: Different databases may handle ties differently, affecting running totals unless window frames are explicitly defined.

Why it matters: Assuming uniform behavior can cause subtle bugs when moving queries between systems.

Expert Zone

1

Running totals can be combined with other window functions like ROW_NUMBER() to create complex analytics.

2

The choice between RANGE and ROWS in window frames affects how duplicates in ORDER BY are handled, which is often overlooked.

3

Some databases optimize running totals internally using incremental aggregation, but others may scan repeatedly, impacting performance.

When NOT to use

Running totals are not suitable when you need totals that ignore row order or when data is unbounded and streaming; in such cases, approximate or incremental aggregation methods or specialized analytics tools are better.

Production Patterns

In production, running totals are used in financial reports, sales dashboards, and inventory tracking. They often combine PARTITION BY for categories and careful indexing on ORDER BY columns to ensure fast, accurate results.

Connections

Cumulative Distribution Function (CDF)

Both calculate cumulative values step-by-step over ordered data.

Understanding running totals helps grasp how CDFs accumulate probabilities, linking database analytics to statistics.

Prefix Sum in Algorithms

Running totals in SQL are the database equivalent of prefix sums used in programming to speed up range queries.

Knowing prefix sums clarifies how running totals optimize repeated sum calculations efficiently.

Financial Accounting

Running totals mirror ledger balances that accumulate transactions over time.

Recognizing this connection shows how databases support real-world financial tracking and reporting.

Common Pitfalls

#1 Running totals calculated without ORDER BY, causing incorrect sums.

Wrong approach:

SELECT date, amount, SUM(amount) OVER () AS running_total FROM sales;

Correct approach:

SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total FROM sales;

Root cause:

Forgetting that running totals depend on row order, so omitting ORDER BY leads to undefined accumulation.

#2 Running totals not resetting per group when needed.

Wrong approach:

SELECT region, date, amount, SUM(amount) OVER (ORDER BY date) AS running_total FROM sales;

Correct approach:

SELECT region, date, amount, SUM(amount) OVER (PARTITION BY region ORDER BY date) AS running_total FROM sales;

Root cause:

Missing PARTITION BY means totals accumulate over all data, mixing groups unintentionally.

#3 Assuming duplicates in ORDER BY don't affect running totals.

Wrong approach:

SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total FROM sales; -- with duplicate dates

Correct approach:

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

Root cause:

Not controlling window frame leads to unexpected grouping of tied rows, causing incorrect sums.

Key Takeaways

Running totals with SUM OVER calculate cumulative sums step-by-step while keeping all rows visible.

The ORDER BY clause inside OVER is essential to define the sequence for accumulation.

PARTITION BY allows running totals to reset for different groups, enabling group-wise analysis.

Controlling the window frame with ROWS or RANGE prevents subtle bugs with duplicates or NULLs.

Understanding performance and edge cases ensures running totals work correctly and efficiently in real-world systems.

Practice

1. What does the SQL expression SUM(amount) OVER (ORDER BY date) calculate?

easy

A. A running total of the amount column ordered by date

B. The total sum of amount for all rows without order

C. The average amount grouped by date

D. The maximum amount value in the table

Solution

1. Step 1: Understand SUM() with OVER()

   The SUM() function adds values, and OVER() defines how to apply it across rows.

2. Step 2: Analyze ORDER BY in OVER()

   ORDER BY date means the sum accumulates in date order, creating a running total.

3. Final Answer:

   A running total of the amount column ordered by date -> Option A

4. Quick Check:

   SUM() OVER(ORDER BY) = running total [OK]

Hint: SUM() with ORDER BY inside OVER() means running total [OK]

Common Mistakes:

• Thinking SUM() without OVER() gives running total
• Confusing running total with group total
• Ignoring ORDER BY inside OVER()

2. Which of the following is the correct syntax to calculate a running total of sales ordered by sale_date?

easy

A. SELECT sales, SUM(sales) OVER (PARTITION BY sale_date) FROM sales_table;

B. SELECT sales, SUM(sales) OVER (ORDER BY sale_date) FROM sales_table;

C. SELECT sales, SUM(sales) FROM sales_table ORDER BY sale_date;

D. SELECT sales, SUM(sales) OVER () ORDER BY sale_date FROM sales_table;

Solution

1. Step 1: Identify correct use of SUM() OVER()

   Running total requires SUM() OVER with ORDER BY clause inside OVER().

2. Step 2: Check each option's syntax

   SELECT sales, SUM(sales) OVER (ORDER BY sale_date) FROM sales_table; uses SUM(sales) OVER (ORDER BY sale_date), which is correct syntax for running total.

3. Final Answer:

   SELECT sales, SUM(sales) OVER (ORDER BY sale_date) FROM sales_table; -> Option B

4. Quick Check:

   SUM() OVER (ORDER BY ...) = running total syntax [OK]

Hint: Use SUM() OVER (ORDER BY column) for running totals [OK]

Common Mistakes:

• Using PARTITION BY instead of ORDER BY for running total
• Omitting OVER() clause
• Placing ORDER BY outside OVER()

3. Given the table sales_data with columns id, sale_date, and amount, what is the output of this query?

SELECT id, sale_date, amount, SUM(amount) OVER (ORDER BY sale_date) AS running_total FROM sales_data ORDER BY id;

Assume the data:
id | sale_date | amount
1 | 2024-01-01 | 100
2 | 2024-01-03 | 200
3 | 2024-01-02 | 150

medium

A. Running totals: 100, 350, 450 ordered by id

B. Running totals: 100, 150, 350 ordered by id

C. Running totals: 100, 450, 250 ordered by id

D. Running totals: 100, 300, 450 ordered by id

Solution

1. Step 1: Order rows by sale_date for running total

   Order by sale_date: id=1(2024-01-01), id=3(2024-01-02), id=2(2024-01-03).

2. Step 2: Calculate running total in sale_date order

   Running totals: 100 (id=1), 100+150=250 (id=3), 250+200=450 (id=2).

3. Step 3: Output ordered by id

   Order by id: id=1 (100), id=2 (450), id=3 (250).

4. Final Answer:

   Running totals: 100, 450, 250 ordered by id -> Option C

5. Quick Check:

   Running total sums in sale_date order, output sorted by id [OK]

Hint: Running total sums by ORDER BY inside OVER, output order separate [OK]

Common Mistakes:

• Assuming running total follows output ORDER BY
• Adding amounts in id order instead of sale_date order
• Confusing running total with simple sum

4. Identify the error in this query intended to calculate a running total of points ordered by game_date:

SELECT player_id, points, SUM(points) OVER (PARTITION BY player_id) ORDER BY game_date FROM game_scores;

medium

A. ORDER BY clause is outside the OVER() function

B. PARTITION BY cannot be used with SUM()

C. Missing GROUP BY clause

D. SUM() cannot be used with window functions

Solution

1. Step 1: Check placement of ORDER BY

   ORDER BY for running total must be inside OVER(), not outside the function.

2. Step 2: Understand correct syntax

   Correct syntax: SUM(points) OVER (PARTITION BY player_id ORDER BY game_date).

3. Final Answer:

   ORDER BY clause is outside the OVER() function -> Option A

4. Quick Check:

   ORDER BY must be inside OVER() for running totals [OK]

Hint: Put ORDER BY inside OVER() for running totals [OK]

Common Mistakes:

• Placing ORDER BY outside OVER()
• Confusing PARTITION BY with GROUP BY
• Thinking SUM() can't be window function

5. You have a sales table with columns region, sale_date, and amount. You want to calculate a running total of sales per region ordered by sale_date. Which query correctly achieves this?

hard

A. SELECT region, sale_date, amount, SUM(amount) OVER (ORDER BY sale_date) AS running_total FROM sales;

B. SELECT region, sale_date, amount, SUM(amount) OVER (PARTITION BY sale_date ORDER BY region) AS running_total FROM sales;

C. SELECT region, sale_date, amount, SUM(amount) FROM sales GROUP BY region, sale_date ORDER BY sale_date;

D. SELECT region, sale_date, amount, SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total FROM sales;

Solution

1. Step 1: Understand requirement for running total per region

   We need to reset running total for each region, so PARTITION BY region is required.

2. Step 2: Check ORDER BY for running total

   ORDER BY sale_date inside OVER() ensures running total accumulates by date within each region.

3. Step 3: Evaluate options

   SELECT region, sale_date, amount, SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total FROM sales; correctly uses PARTITION BY region and ORDER BY sale_date inside SUM() OVER().

4. Final Answer:

   SELECT region, sale_date, amount, SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total FROM sales; -> Option D

5. Quick Check:

   PARTITION BY region + ORDER BY sale_date = running total per region [OK]

Hint: Use PARTITION BY for groups, ORDER BY for running total order [OK]

Common Mistakes:

• Omitting PARTITION BY to separate groups
• Using GROUP BY instead of window functions
• Swapping ORDER BY columns inside OVER()