Running total without window functions in SQL - Deep Dive

Overview - Running total without window functions

What is it?

A running total is a sum that adds up values from a list step-by-step, showing the total at each point. Without window functions, which are special SQL tools for this, we use other methods to calculate this cumulative sum. This topic teaches how to get running totals using basic SQL features. It helps when your database does not support advanced functions.

Why it matters

Running totals help track progress over time, like daily sales adding up to monthly totals. Without ways to calculate running totals, you would need to do this outside the database, making reports slower and more complex. Learning to do this without window functions means you can work with older or simpler databases and still get useful cumulative data.

Where it fits

Before this, you should understand basic SQL queries, SELECT statements, and simple aggregation like SUM(). After this, you can learn window functions for more efficient running totals and advanced analytics.

Mental Model

Core Idea

A running total adds each new value to the sum of all previous values step-by-step, like counting money in your hand one bill at a time.

Think of it like...

Imagine filling a jar with coins one by one and noting the total amount after each coin. You don't have a special tool; you just keep adding the new coin to the amount you already counted.

Table: Sales
┌─────┬────────┬─────────────┐
│ ID  │ Amount │ RunningSum  │
├─────┼────────┼─────────────┤
│ 1   │ 10     │ 10          │
│ 2   │ 20     │ 30          │
│ 3   │ 15     │ 45          │
└─────┴────────┴─────────────┘

Process:
For each row, sum all amounts from rows with ID less or equal to current.

Build-Up - 7 Steps

1

Foundation

Understanding basic aggregation with SUM

Concept: Learn how to use the SUM() function to add values in a column.

The SUM() function adds all values in a column. For example, SELECT SUM(amount) FROM sales; adds all sales amounts together.

Result

Returns one number: the total sum of all amounts.

Knowing how SUM() works is essential because running totals build on adding values step-by-step.

2

Foundation

Filtering rows with WHERE clause

Concept: Learn how to select specific rows using conditions.

The WHERE clause filters rows. For example, SELECT * FROM sales WHERE id <= 3; selects rows with id 1, 2, and 3.

Result

Returns only rows that meet the condition.

Filtering rows lets us sum only the values up to a certain point, which is key for running totals.

3

Intermediate

Calculating running total with correlated subquery

🤔Before reading on: do you think a subquery can access the outer query's current row value? Commit to yes or no.

Concept: Use a subquery that sums values up to the current row's ID to get running totals.

For each row, run a subquery that sums amounts where id is less or equal to the current row's id. Example: SELECT s1.id, s1.amount, (SELECT SUM(s2.amount) FROM sales s2 WHERE s2.id <= s1.id) AS running_total FROM sales s1 ORDER BY s1.id;

Result

Returns each row with its amount and the running total up to that row.

Understanding correlated subqueries lets you compute cumulative sums without special functions.

4

Intermediate

Performance considerations of subqueries

🤔Before reading on: do you think running a subquery for each row is fast or slow? Commit to your answer.

Concept: Recognize that correlated subqueries run once per row, which can slow down queries on large data.

Because the subquery sums amounts for each row, the database repeats work many times. This can cause slow performance on big tables.

Result

Query works but may be slow with many rows.

Knowing performance limits helps decide when to use this method or seek alternatives.

5

Advanced

Using self-join for running total calculation

🤔Before reading on: can joining a table to itself help calculate running totals? Commit to yes or no.

Concept: Join the table to itself to sum all rows with IDs less or equal to the current row's ID.

Example: SELECT s1.id, s1.amount, SUM(s2.amount) AS running_total FROM sales s1 JOIN sales s2 ON s2.id <= s1.id GROUP BY s1.id, s1.amount ORDER BY s1.id;

Result

Returns each row with amount and running total, calculated by joining rows up to current.

Using self-joins is an alternative to subqueries and can be more efficient in some databases.

6

Advanced

Limitations and edge cases without window functions

🤔Before reading on: do you think these methods handle ties or unordered data well? Commit to yes or no.

Concept: Understand that without window functions, handling ties or ordering can be tricky and may require extra care.

If IDs are not unique or data is unordered, running totals may be incorrect. You must ensure a clear order and unique keys.

Result

Running totals may be wrong if ordering is ambiguous.

Knowing data requirements prevents wrong results and confusion.

7

Expert

Optimizing running totals with indexed columns

🤔Before reading on: do you think indexes affect running total query speed? Commit to yes or no.

Concept: Using indexes on the ordering column (like ID) can speed up subqueries and joins for running totals.

If the ID column is indexed, the database can quickly find rows with id <= current id, improving performance. Example: CREATE INDEX idx_sales_id ON sales(id);

Result

Running total queries run faster on large tables with proper indexing.

Understanding indexing helps optimize queries that otherwise run slowly due to repeated scans.

Under the Hood

Without window functions, running totals are computed by summing all rows up to the current one repeatedly. This is done via correlated subqueries or self-joins. Each row triggers a separate sum operation over a subset of rows, causing repeated work. The database engine executes these sums by scanning or using indexes to find matching rows.

Why designed this way?

Window functions were introduced to simplify and speed up cumulative calculations by avoiding repeated scans. Before they existed, databases relied on basic SQL features like subqueries and joins. These methods are more general but less efficient. The design tradeoff was between simplicity and performance.

Running total calculation flow:

┌─────────────┐
│ Outer Query │
│ (each row)  │
└─────┬───────┘
      │
      ▼
┌─────────────────────────────┐
│ Correlated Subquery or Join  │
│ sums rows with id <= current │
└─────────────┬───────────────┘
              │
              ▼
      ┌─────────────┐
      │ Data Table  │
      └─────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does a correlated subquery run once or multiple times for a query? Commit to one.

Common Belief: A correlated subquery runs only once per query, so it is fast.

Reality: A correlated subquery runs once for each row in the outer query, which can be slow on large data.

Why it matters: Assuming it runs once leads to unexpected slow queries and poor performance.

Quick: Can running totals be calculated correctly without ordering? Commit yes or no.

Common Belief: Running totals work fine even if rows are unordered or IDs are not unique.

Reality: Running totals require a clear order and unique keys to be correct; otherwise, sums can be wrong or inconsistent.

Why it matters: Ignoring ordering causes wrong cumulative sums, misleading reports, and bad decisions.

Quick: Is using self-join always faster than correlated subqueries? Commit yes or no.

Common Belief: Self-joins are always faster than correlated subqueries for running totals.

Reality: Performance depends on the database and data size; sometimes self-joins are faster, sometimes subqueries are better.

Why it matters: Choosing blindly can cause inefficient queries and wasted resources.

Quick: Can you get running totals without any aggregation functions? Commit yes or no.

Common Belief: You can calculate running totals without using SUM() or any aggregation.

Reality: Aggregation like SUM() is necessary to add values cumulatively; without it, you cannot get running totals in SQL.

Why it matters: Trying to avoid aggregation leads to incorrect or impossible calculations.

Expert Zone

1

Correlated subqueries can sometimes be optimized by the database engine into joins or indexed scans, but this depends on the SQL engine's capabilities.

2

When IDs are not numeric or sequential, you must carefully define the ordering logic, possibly using timestamps or composite keys, to get correct running totals.

3

Some databases allow user-defined variables or procedural code to compute running totals more efficiently without window functions, but these are non-standard and less portable.

When NOT to use

Avoid these methods on very large datasets where performance is critical; instead, use window functions if available. If window functions are not supported, consider pre-aggregating data or using application-level logic. For unordered or complex data, these methods may produce incorrect results.

Production Patterns

In production, running totals without window functions are often used in legacy systems or simple reporting tools. Developers use indexed columns and limit data size to keep queries efficient. Sometimes, materialized views or summary tables are created to avoid repeated calculations.

Connections

Window functions

Builds-on

Understanding running totals without window functions prepares you to appreciate how window functions simplify and speed up cumulative calculations.

Algorithmic prefix sums

Same pattern

Running totals in SQL are like prefix sums in programming, where each element stores the sum of all previous elements, showing a shared concept across fields.

Financial accounting

Application domain

Running totals are fundamental in accounting to track balances over time, showing how database queries support real-world financial tracking.

Common Pitfalls

#1 Calculating running total without ordering rows

Wrong approach:

SELECT id, amount, (SELECT SUM(amount) FROM sales) AS running_total FROM sales;

Correct approach:

SELECT s1.id, s1.amount, (SELECT SUM(s2.amount) FROM sales s2 WHERE s2.id <= s1.id) AS running_total FROM sales s1 ORDER BY s1.id;

Root cause:

Not specifying an order means the sum is total for all rows, not cumulative up to current row.

#2 Using non-unique or unordered column for running total

Wrong approach:

SELECT s1.id, s1.amount, (SELECT SUM(s2.amount) FROM sales s2 WHERE s2.date <= s1.date) AS running_total FROM sales s1 ORDER BY s1.date;

Correct approach:

Ensure unique ordering, e.g., SELECT s1.id, s1.amount, (SELECT SUM(s2.amount) FROM sales s2 WHERE s2.id <= s1.id) AS running_total FROM sales s1 ORDER BY s1.id;

Root cause:

Using date alone may cause ties or unordered rows, leading to incorrect sums.

#3 Ignoring performance impact of correlated subqueries

Wrong approach:

SELECT s1.id, s1.amount, (SELECT SUM(s2.amount) FROM sales s2 WHERE s2.id <= s1.id) AS running_total FROM sales s1;

Correct approach:

Create index on id: CREATE INDEX idx_sales_id ON sales(id); Then run the same query for better speed.

Root cause:

Not indexing the column used in subquery conditions causes slow repeated scans.

Key Takeaways

Running totals add values step-by-step, showing cumulative sums at each row.

Without window functions, running totals are calculated using correlated subqueries or self-joins that sum all previous rows.

Correct ordering and unique keys are essential to get accurate running totals.

These methods can be slow on large data, so indexing and query optimization are important.

Understanding these basics prepares you to use window functions and advanced SQL features later.

Practice

1. What is the main idea behind calculating a running total without using window functions in SQL?

easy

A. Use GROUP BY to group all rows together.

B. Use the SUM() function without any conditions.

C. Use a JOIN to combine unrelated tables.

D. Use a subquery to sum all previous rows up to the current row.

Solution

1. Step 1: Understand running total concept

   A running total means adding values step-by-step, including all previous rows up to the current one.

2. Step 2: Identify method without window functions

   Without window functions, a common way is to use a subquery that sums all rows with an ID less than or equal to the current row's ID.

3. Final Answer:

   Use a subquery to sum all previous rows up to the current row. -> Option D

4. Quick Check:

   Running total = cumulative sum using subquery [OK]

Hint: Think cumulative sum using subquery with condition [OK]

Common Mistakes:

• Using SUM() without filtering rows
• Trying to use GROUP BY for running total
• Confusing JOIN with running total calculation

2. Which of the following SQL queries correctly calculates a running total of the amount column without using window functions, assuming id orders the rows?

easy

A. SELECT id, amount, (SELECT SUM(amount) FROM sales WHERE id <= s.id) AS running_total FROM sales s ORDER BY id;

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

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

D. SELECT id, amount, amount + LAG(amount) FROM sales ORDER BY id;

Solution

1. Step 1: Identify correct syntax without window functions

   SELECT id, amount, (SELECT SUM(amount) FROM sales WHERE id <= s.id) AS running_total FROM sales s ORDER BY id; uses a correlated subquery summing amounts where id is less or equal to current row's id, which is correct for running total without window functions.

2. Step 2: Check other options for errors

   SELECT id, amount, SUM(amount) OVER (ORDER BY id) AS running_total FROM sales ORDER BY id; uses window function SUM() OVER, which is not allowed here. SELECT id, amount, SUM(amount) FROM sales GROUP BY id ORDER BY id; misuses GROUP BY without running total logic. SELECT id, amount, amount + LAG(amount) FROM sales ORDER BY id; uses LAG() which is a window function.

3. Final Answer:

   SELECT id, amount, (SELECT SUM(amount) FROM sales WHERE id <= s.id) AS running_total FROM sales s ORDER BY id; -> Option A

4. Quick Check:

   Subquery sums previous rows correctly [OK]

Hint: Look for subquery with condition id <= current id [OK]

Common Mistakes:

• Using window functions despite instructions
• Misusing GROUP BY without aggregation logic
• Using LAG() which is a window function

3. Given the table sales with rows:
id | amount
1 | 10
2 | 20
3 | 15
What is the output of this query?

SELECT id, amount, (SELECT SUM(amount) FROM sales WHERE id <= s.id) AS running_total FROM sales s ORDER BY id;

medium

A. 1 | 10 | 102 | 20 | 203 | 15 | 15

B. 1 | 10 | 102 | 20 | 303 | 15 | 45

C. 1 | 10 | 102 | 20 | 253 | 15 | 40

D. 1 | 10 | 102 | 20 | 303 | 15 | 15

Solution

1. Step 1: Calculate running total for each row

   For id=1: sum amounts where id <=1 is 10.
   For id=2: sum amounts where id <=2 is 10+20=30.
   For id=3: sum amounts where id <=3 is 10+20+15=45.

2. Step 2: Match calculated values with options

   1 | 10 | 10
   2 | 20 | 30
   3 | 15 | 45 matches these running totals exactly.

3. Final Answer:

   1 | 10 | 10
   2 | 20 | 30
   3 | 15 | 45 -> Option B

4. Quick Check:

   Running total sums all previous rows [OK]

Hint: Add amounts cumulatively by id order [OK]

Common Mistakes:

• Summing only current row amount
• Not including all previous rows in sum
• Mixing up running total values

4. Consider this query intended to calculate a running total:

SELECT id, amount, (SELECT SUM(amount) FROM sales WHERE id < s.id) AS running_total FROM sales s ORDER BY id;

What is the main issue with this query?

medium

A. It will cause a syntax error due to missing GROUP BY.

B. It uses window functions which are not allowed.

C. It excludes the current row's amount from the running total.

D. It sums amounts from unrelated tables.

Solution

1. Step 1: Analyze the subquery condition

   The subquery sums amounts where id is less than current row's id, so it excludes the current row's amount.

2. Step 2: Understand running total definition

   Running total should include the current row's amount, so condition should be id <= s.id, not id < s.id.

3. Final Answer:

   It excludes the current row's amount from the running total. -> Option C

4. Quick Check:

   Use id <= current id to include current row [OK]

Hint: Check if current row is included in sum condition [OK]

Common Mistakes:

• Using < instead of <= in subquery condition
• Expecting window functions in this query
• Assuming syntax error due to GROUP BY

5. You have a transactions table with columns trans_id, user_id, and amount. You want to calculate a running total of amount for each user_id ordered by trans_id, but you cannot use window functions. Which query correctly achieves this?

hard

A. SELECT t1.trans_id, t1.user_id, t1.amount, (SELECT SUM(t2.amount) FROM transactions t2 WHERE t2.user_id = t1.user_id AND t2.trans_id <= t1.trans_id) AS running_total FROM transactions t1 ORDER BY t1.user_id, t1.trans_id;

B. SELECT trans_id, user_id, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY trans_id) AS running_total FROM transactions ORDER BY user_id, trans_id;

C. SELECT trans_id, user_id, amount, SUM(amount) FROM transactions GROUP BY user_id, trans_id ORDER BY user_id, trans_id;

D. SELECT t1.trans_id, t1.user_id, t1.amount, (SELECT SUM(amount) FROM transactions WHERE trans_id <= t1.trans_id) AS running_total FROM transactions t1 ORDER BY user_id, trans_id;

Solution

1. Step 1: Understand requirement for per-user running total

   Running total must reset for each user_id and sum amounts up to current trans_id.

2. Step 2: Analyze options for correct filtering

   SELECT t1.trans_id, t1.user_id, t1.amount, (SELECT SUM(t2.amount) FROM transactions t2 WHERE t2.user_id = t1.user_id AND t2.trans_id <= t1.trans_id) AS running_total FROM transactions t1 ORDER BY t1.user_id, t1.trans_id; uses a correlated subquery filtering by same user_id and trans_id less or equal current, correctly calculating running total per user.

3. Step 3: Identify errors in other options

   SELECT trans_id, user_id, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY trans_id) AS running_total FROM transactions ORDER BY user_id, trans_id; uses window functions (not allowed). SELECT trans_id, user_id, amount, SUM(amount) FROM transactions GROUP BY user_id, trans_id ORDER BY user_id, trans_id; misuses GROUP BY without running total logic. SELECT t1.trans_id, t1.user_id, t1.amount, (SELECT SUM(amount) FROM transactions WHERE trans_id <= t1.trans_id) AS running_total FROM transactions t1 ORDER BY user_id, trans_id; sums amounts ignoring user_id, mixing all users.

4. Final Answer:

   SELECT t1.trans_id, t1.user_id, t1.amount, (SELECT SUM(t2.amount) FROM transactions t2 WHERE t2.user_id = t1.user_id AND t2.trans_id <= t1.trans_id) AS running_total FROM transactions t1 ORDER BY t1.user_id, t1.trans_id; -> Option A

5. Quick Check:

   Subquery filters by user and trans_id for running total [OK]

Hint: Filter subquery by user_id and trans_id <= current [OK]

Common Mistakes:

• Ignoring user_id in running total calculation
• Using window functions despite restriction
• Not ordering results properly