Running total without window functions in SQL - Step-by-Step Execution

Concept Flow - Running total without window functions

Start with first row

↓

Calculate sum of all previous rows

↓

Add current row's value

↓

Store running total for current row

↓

Move to next row

↓

Repeat until last row

↓

End with full running total column

For each row, sum all previous rows' values plus the current one to get a running total, repeating until all rows are processed.

Execution Sample

SQL

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

This query calculates a running total of 'amount' by summing all amounts with id less than or equal to the current row's id.

Execution Table

Step	Current Row id	Subquery Condition	Subquery Sum	Running Total
1	1	b.id <= 1	100	100
2	2	b.id <= 2	100 + 200 = 300	300
3	3	b.id <= 3	100 + 200 + 150 = 450	450
4	4	b.id <= 4	100 + 200 + 150 + 50 = 500	500
5	5	b.id <= 5	100 + 200 + 150 + 50 + 300 = 800	800
Exit	-	-	-	All rows processed

💡 All rows processed, running total calculated for each row.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
current_row_id	-	1	2	3	4	5	5
running_total	0	100	300	450	500	800	800

Key Moments - 2 Insights

Why does the subquery use 'b.id <= a.id' instead of just 'b.id = a.id'?

Because to get the running total, we need to sum all amounts from rows with id less than or equal to the current row's id, not just the current row alone. This is shown in execution_table rows where the subquery sums multiple rows.

Does this method work if ids are not sequential or have gaps?

Yes, as long as the condition 'b.id <= a.id' correctly identifies all previous rows. The running total sums all rows with id less or equal to current, regardless of gaps.

Visual Quiz - 3 Questions

Look at the execution table, what is the running total at step 3?

A 450

B 150

C 300

D 500

💡 Hint

Check the 'Running Total' column at step 3 in the execution_table.

At which step does the running total first exceed 400?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look at the 'Running Total' values in execution_table rows to find when it passes 400.

If the subquery condition changed to 'b.id < a.id', what would be the running total at step 1?

A 200

B 100

C 0

D 300

💡 Hint

Refer to execution_table and think what happens if current row's id is excluded from sum.

Concept Snapshot

Running total without window functions:
Use a correlated subquery to sum all previous rows plus current.
Syntax example: (SELECT SUM(b.amount) FROM table b WHERE b.id <= a.id)
Works even if ids have gaps.
Slower on large data but simple to understand.

Full Transcript

This visual execution shows how to calculate a running total in SQL without window functions. For each row in the sales table, the query sums all amounts from rows with id less than or equal to the current row's id. The execution table traces each step, showing the current row id, the subquery condition, the sum calculated, and the running total result. Variables like current_row_id and running_total update step by step. Key moments clarify why the subquery uses 'b.id <= a.id' and that this method works even if ids are not sequential. The quiz tests understanding of running total values at specific steps and the effect of changing the subquery condition. The snapshot summarizes the approach and syntax for quick reference.