Running totals are one of the most requested patterns in analytical SQL — and GROUP BY alone can't produce them. Learn two reliable techniques using correlated subqueries and self-joins, with real-world examples covering revenue, user growth, and multi-category breakdowns.
Imagine you're a data analyst at a SaaS company. The VP of Sales drops into your Slack at 9am: "Can you send me a chart showing how our revenue has built up month by month this year? I want to see the cumulative number, not just monthly totals." You know how to sum revenue by month using GROUP BY. But a running total — where each row reflects everything that came before it plus itself — requires a different kind of thinking.
Running totals and cumulative sums are one of the most requested analytical patterns in SQL. They show up everywhere: cumulative revenue, rolling user counts, running inventory levels, progressive test scores. The challenge is that standard GROUP BY collapses multiple rows into one. A running total asks you to do the opposite — for each row, look backward at a set of other rows and aggregate across them. That's a fundamentally different operation, and getting it right requires understanding how subqueries can reach outside themselves and reference the query they're embedded in.
By the end of this lesson, you'll have two reliable techniques for calculating running totals using tools already in your SQL toolkit: self-referencing subqueries in the SELECT clause and correlated subqueries joined to aggregated results. You'll understand why each approach works, where each one breaks down, and how to debug the subtle ordering bugs that make running totals go wrong.
What you'll learn:
You should be comfortable writing GROUP BY queries with aggregate functions like SUM and COUNT. If you need a refresher, start with Grouping and Summarizing Data: COUNT, SUM, AVG, and GROUP BY for Beginners before continuing.
You should also understand how subqueries work — specifically how a subquery in the SELECT clause executes once per output row. If that's fuzzy, Understanding SQL Subqueries: Filtering and Looking Up Data with Nested SELECT Statements will get you up to speed.
Let's build our working dataset. We're analyzing monthly sales for an e-commerce business. We have an orders table:
-- orders table structure
-- order_id INT
-- order_date DATE
-- customer_id INT
-- revenue DECIMAL(10,2)
A simple monthly summary is straightforward:
SELECT
DATE_TRUNC('month', order_date) AS order_month,
SUM(revenue) AS monthly_revenue
FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2025-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;
This gives you something like:
order_month | monthly_revenue
------------+----------------
2024-01-01 | 48200.00
2024-02-01 | 51300.00
2024-03-01 | 63100.00
2024-04-01 | 57800.00
...
Each row is self-contained. January knows nothing about February. GROUP BY has done its job: it collapsed all orders within a month into a single row. But now you want a third column — cumulative_revenue — where January shows 48200, February shows 99500 (48200 + 51300), March shows 162600, and so on.
GROUP BY can't do this. Once rows are grouped, the engine has no mechanism to reach across groups and accumulate values from previous ones. What you need is a way to say: "for this month's row, give me the SUM of monthly_revenue for all months that came before it (including itself)."
That's a correlated subquery.
A correlated subquery is a subquery that references a column from the outer query. It re-executes for every row the outer query produces, which is exactly the behavior you need: each row triggers a fresh calculation that looks at a specific slice of data.
Here's the pattern for a running total. We'll use a CTE first to separate concerns — getting monthly totals is one step, computing the running total is another:
WITH monthly_revenue AS (
SELECT
DATE_TRUNC('month', order_date) AS order_month,
SUM(revenue) AS monthly_revenue
FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2025-01-01'
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
m.order_month,
m.monthly_revenue,
(
SELECT SUM(m2.monthly_revenue)
FROM monthly_revenue m2
WHERE m2.order_month <= m.order_month
) AS cumulative_revenue
FROM monthly_revenue m
ORDER BY m.order_month;
This produces:
order_month | monthly_revenue | cumulative_revenue
------------+-----------------+-------------------
2024-01-01 | 48200.00 | 48200.00
2024-02-01 | 51300.00 | 99500.00
2024-03-01 | 63100.00 | 162600.00
2024-04-01 | 57800.00 | 220400.00
...
Let's trace through why this works. The outer query produces one row per month from monthly_revenue. For each of those rows — let's say it's the March row — the subquery runs with the value of m.order_month substituted in. So the subquery becomes effectively:
SELECT SUM(m2.monthly_revenue)
FROM monthly_revenue m2
WHERE m2.order_month <= '2024-03-01'
That matches January, February, and March — and sums them. When the outer query moves to April's row, the subquery re-runs with '2024-04-01' as the cutoff. The <= operator is the engine of the whole pattern: it defines "everything up to and including this point."
Key insight
The correlated subquery doesn't "know" about row order. It knows about values. The <= comparison is what creates the cumulative logic — you're summing all months whose month-value is less than or equal to the current row's month-value. This means your ordering column must be something meaningful to compare with <=, like a date or an integer sequence, not an arbitrary string.
Sometimes you're working in an environment where CTEs aren't available, or you want to write the whole thing as a single query. You can embed the GROUP BY directly as a derived table and apply the correlated subquery to it:
SELECT
m.order_month,
m.monthly_revenue,
(
SELECT SUM(m2.monthly_revenue)
FROM (
SELECT
DATE_TRUNC('month', order_date) AS order_month,
SUM(revenue) AS monthly_revenue
FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2025-01-01'
GROUP BY DATE_TRUNC('month', order_date)
) m2
WHERE m2.order_month <= m.order_month
) AS cumulative_revenue
FROM (
SELECT
DATE_TRUNC('month', order_date) AS order_month,
SUM(revenue) AS monthly_revenue
FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2025-01-01'
GROUP BY DATE_TRUNC('month', order_date)
) m
ORDER BY m.order_month;
This is functionally identical to the CTE version. The derived table in the FROM clause is m, and the correlated subquery also contains its own copy of the same derived table as m2. The duplication is the main downside — if you need to change the date filter, you have to change it in two places. The CTE version avoids this by defining the monthly aggregation once and referencing it twice.
Tip
When you find yourself writing the same subquery in two places, that's a signal to convert to a CTE. The CTE approach is also easier to debug because you can run just the CTE portion and inspect its output before adding the correlated subquery on top.
Real business questions add a dimension. The VP of Sales comes back: "Actually, can you break that down by product category? I want to see cumulative revenue for each category independently."
This is where the pattern gets more interesting. You need the correlated subquery to also filter by category — so March's cumulative total for "Electronics" only accumulates Electronics revenue, not all categories.
WITH monthly_category_revenue AS (
SELECT
p.category,
DATE_TRUNC('month', o.order_date) AS order_month,
SUM(o.revenue) AS monthly_revenue
FROM orders o
JOIN products p ON o.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
AND o.order_date < '2025-01-01'
GROUP BY p.category, DATE_TRUNC('month', o.order_date)
)
SELECT
m.category,
m.order_month,
m.monthly_revenue,
(
SELECT SUM(m2.monthly_revenue)
FROM monthly_category_revenue m2
WHERE m2.category = m.category -- same category
AND m2.order_month <= m.order_month -- up to this month
) AS cumulative_revenue
FROM monthly_category_revenue m
ORDER BY m.category, m.order_month;
The addition of m2.category = m.category in the correlated subquery creates an independent running total for each category. Without that condition, every row would accumulate revenue across all categories — a common mistake we'll cover in the troubleshooting section.
The result would look like:
category | order_month | monthly_revenue | cumulative_revenue
-------------+-------------+-----------------+-------------------
Electronics | 2024-01-01 | 22100.00 | 22100.00
Electronics | 2024-02-01 | 19800.00 | 41900.00
Electronics | 2024-03-01 | 31200.00 | 73100.00
Furniture | 2024-01-01 | 14600.00 | 14600.00
Furniture | 2024-02-01 | 18500.00 | 33100.00
...
Note
The JOIN in the CTE pulls in the products table to get the category column. If you're new to combining aggregates across multiple tables, Multi-Table Reporting with JOIN and GROUP BY: Aggregating Across Relationships in a Single Query covers this pattern in depth.
Running totals work for more than revenue. Here's a pattern you'll use constantly: tracking how your user base grows over time. You want to know, as of each day, how many total users had registered up to that point.
WITH daily_signups AS (
SELECT
DATE_TRUNC('day', created_at) AS signup_date,
COUNT(user_id) AS new_users
FROM users
WHERE created_at >= '2024-01-01'
AND created_at < '2024-04-01'
GROUP BY DATE_TRUNC('day', created_at)
)
SELECT
d.signup_date,
d.new_users,
(
SELECT SUM(d2.new_users)
FROM daily_signups d2
WHERE d2.signup_date <= d.signup_date
) AS total_users_to_date
FROM daily_signups d
ORDER BY d.signup_date;
This is the same structural pattern as the revenue example. The only changes are the source table (users), the metric (COUNT(user_id) instead of SUM(revenue)), and the time granularity (day instead of month). Once you internalize the pattern, you can adapt it to almost any cumulative metric.
Warning
When using daily granularity over a long period, the correlated subquery approach starts to show performance strain. For each of N days, the subquery scans up to N previous rows. That's O(N²) complexity. For 90 days, that's roughly 8,100 subquery executions. For 3 years of daily data (~1,095 rows), it's over a million subquery operations. On large datasets, consider moving to window functions if your database supports them.
Another way to compute running totals without window functions is a self-join approach. Instead of a correlated subquery in the SELECT clause, you join the monthly totals table to itself, matching "all prior months":
WITH monthly_revenue AS (
SELECT
DATE_TRUNC('month', order_date) AS order_month,
SUM(revenue) AS monthly_revenue
FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2025-01-01'
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
m1.order_month,
m1.monthly_revenue,
SUM(m2.monthly_revenue) AS cumulative_revenue
FROM monthly_revenue m1
JOIN monthly_revenue m2
ON m2.order_month <= m1.order_month
GROUP BY m1.order_month, m1.monthly_revenue
ORDER BY m1.order_month;
The self-join works like this: for every row in m1, you join to all rows in m2 where m2.order_month <= m1.order_month. The March row in m1 joins to January, February, and March in m2. Then the outer GROUP BY collapses those three joined rows back into one, and SUM adds up their monthly_revenue values.
This gives identical results to the correlated subquery version. The choice between them is largely stylistic, though the self-join version can be slightly easier to reason about because the join condition makes the "which rows are included" logic explicit rather than implicit in a subquery.
Key insight
The self-join pattern creates a triangle of data. Twelve months of data produce a self-joined result set with 78 rows (1+2+3+...+12) before the final GROUP BY collapses them. This is still far more efficient than you might fear for small to medium datasets, but it grows quadratically — the same O(N²) characteristic as the correlated subquery approach.
You can read more about how self-joins work structurally if the ON condition in a join to the same table feels unfamiliar.
Real date sequences have gaps. If no orders came in on a particular day, there's no row for that day in your aggregated result — and that means your running total will skip days. For charting purposes, this often creates visual artifacts. For business reporting, it can make period-over-period comparisons misleading.
The fix requires generating a complete date spine and LEFT JOINing your actuals onto it. Here's one approach using a recursive CTE to generate dates (supported in PostgreSQL, SQL Server, and most modern databases):
WITH RECURSIVE date_spine AS (
SELECT DATE '2024-01-01' AS dt
UNION ALL
SELECT dt + INTERVAL '1 month'
FROM date_spine
WHERE dt < DATE '2024-12-01'
),
monthly_revenue AS (
SELECT
DATE_TRUNC('month', order_date) AS order_month,
SUM(revenue) AS monthly_revenue
FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2025-01-01'
GROUP BY DATE_TRUNC('month', order_date)
),
filled_months AS (
SELECT
ds.dt AS order_month,
COALESCE(mr.monthly_revenue, 0) AS monthly_revenue
FROM date_spine ds
LEFT JOIN monthly_revenue mr ON mr.order_month = ds.dt
)
SELECT
f.order_month,
f.monthly_revenue,
(
SELECT SUM(f2.monthly_revenue)
FROM filled_months f2
WHERE f2.order_month <= f.order_month
) AS cumulative_revenue
FROM filled_months f
ORDER BY f.order_month;
The COALESCE(mr.monthly_revenue, 0) on the LEFT JOIN ensures months with no orders show up as zero revenue rather than NULL. Without this, a NULL month would make the correlated subquery's SUM return NULL for all subsequent months (since NULL + anything = NULL in SQL). This is one of the most common silent data bugs in running total queries.
Warning
NULL propagation in SUM is a silent killer in running totals. If any row in your cumulative window contains a NULL value for the metric column, SUM will ignore it (SUM ignores NULLs, unlike addition). But if your metric column is NULL because of a failed COALESCE on a LEFT JOIN, you'll get incorrect partial sums. Always wrap nullable metric columns in COALESCE before they reach the running total calculation. The NULL handling guide covers this in depth.
Let's build something you could actually hand to a BI tool. The scenario: you're preparing the data layer for a sales dashboard that needs to show, for each sales rep:
Here's the complete query:
WITH rep_monthly AS (
-- Step 1: Aggregate orders by rep and month
SELECT
o.sales_rep_id,
sr.rep_name,
DATE_TRUNC('month', o.order_date) AS order_month,
SUM(o.revenue) AS monthly_revenue
FROM orders o
JOIN sales_reps sr ON sr.rep_id = o.sales_rep_id
WHERE o.order_date >= '2024-01-01'
AND o.order_date < '2025-01-01'
GROUP BY o.sales_rep_id, sr.rep_name, DATE_TRUNC('month', o.order_date)
),
rep_quotas AS (
-- Step 2: Pull annual quota per rep
SELECT
rep_id,
annual_quota
FROM sales_quotas
WHERE quota_year = 2024
)
SELECT
m.rep_name,
m.order_month,
m.monthly_revenue,
-- Correlated subquery for running total, partitioned by rep
(
SELECT SUM(m2.monthly_revenue)
FROM rep_monthly m2
WHERE m2.sales_rep_id = m.sales_rep_id
AND m2.order_month <= m.order_month
) AS cumulative_revenue,
q.annual_quota,
-- What percentage of quota has been reached cumulatively?
ROUND(
(
SELECT SUM(m2.monthly_revenue)
FROM rep_monthly m2
WHERE m2.sales_rep_id = m.sales_rep_id
AND m2.order_month <= m.order_month
) / NULLIF(q.annual_quota, 0) * 100,
1
) AS pct_of_quota
FROM rep_monthly m
LEFT JOIN rep_quotas q ON q.rep_id = m.sales_rep_id
ORDER BY m.rep_name, m.order_month;
A few things worth noting in this query:
The NULLIF(q.annual_quota, 0) prevents a division-by-zero error if a rep has a quota of zero entered in the system. Divide-by-zero in SQL throws an error in most databases; NULLIF converts zero to NULL, which makes the division return NULL rather than crash. This is a production-safety habit worth developing.
The correlated subquery appears twice — once for cumulative_revenue and once inside the pct_of_quota calculation. This is a readability cost you pay when avoiding window functions. If you find yourself repeating it more than twice, that's a strong argument for moving the logic into a second CTE layer.
The LEFT JOIN on quotas ensures reps who don't have a quota entry still appear in results — their pct_of_quota will just be NULL. An INNER JOIN would silently drop those reps from the output.
Tip
When building multi-step analytical queries like this, write and test each CTE independently before combining them. Run SELECT * FROM rep_monthly LIMIT 10 first, verify the monthly aggregation looks right, then add the quota join, then add the correlated subquery. Debugging is much easier when you isolate each layer. The article on building complete analytical queries from scratch walks through this incremental approach in detail.
Work through this exercise using either a local database or a free online SQL sandbox (like db-fiddle.com or SQLiteOnline.com).
Setup: Create and populate this table:
CREATE TABLE website_traffic (
session_date DATE,
channel VARCHAR(50),
sessions INT,
conversions INT
);
INSERT INTO website_traffic VALUES
('2024-01-01', 'Organic', 1240, 38),
('2024-01-01', 'Paid', 870, 52),
('2024-02-01', 'Organic', 1380, 44),
('2024-02-01', 'Paid', 940, 61),
('2024-03-01', 'Organic', 1520, 49),
('2024-03-01', 'Paid', 1100, 74),
('2024-04-01', 'Organic', 1690, 53),
('2024-04-01', 'Paid', 980, 68),
('2024-05-01', 'Organic', 1850, 61),
('2024-05-01', 'Paid', 1220, 82),
('2024-06-01', 'Organic', 2010, 67),
('2024-06-01', 'Paid', 1350, 91);
Part 1 — Basic running total: Write a query that shows, for all channels combined, the monthly total sessions and the cumulative sessions for the year so far.
Part 2 — Running total by channel: Modify your query to show cumulative sessions separately for Organic and Paid channels. Each channel should reset and accumulate independently.
Part 3 — Cumulative conversion rate: Add a column showing the cumulative conversion rate: total conversions to date divided by total sessions to date, expressed as a percentage rounded to two decimal places. Think carefully about whether to compute this from the raw numbers or from the already-aggregated running totals.
Part 4 — Self-join version: Rewrite your Part 1 solution using the self-join approach instead of a correlated subquery. Verify you get identical results.
Expected output for Part 1:
session_month | monthly_sessions | cumulative_sessions
--------------+------------------+--------------------
2024-01-01 | 2110 | 2110
2024-02-01 | 2320 | 4430
2024-03-01 | 2620 | 7050
2024-04-01 | 2670 | 9720
2024-05-01 | 3070 | 12790
2024-06-01 | 3360 | 16150
This is the most common error. You have running totals by category or by sales rep, and you forget to add the WHERE m2.category = m.category condition in the correlated subquery. The result: every row accumulates across all categories, producing wildly inflated numbers that are hard to catch unless you know what the correct answer should be.
Diagnosis: Check whether your cumulative total for the last period of one category matches the sum of all categories' first-period totals. If it does, you're missing the partition condition.
Fix: Add the matching condition for every grouping column to the correlated subquery's WHERE clause.
Some people try to produce running totals by relying on row order rather than value comparison:
-- WRONG - don't do this
SELECT
order_month,
monthly_revenue,
SUM(monthly_revenue) OVER (ORDER BY order_month) -- window function
FROM monthly_revenue;
Wait — that's actually a window function, which is correct syntax. The mistake I'm pointing at here is when people try to hack running totals using LIMIT and OFFSET inside subqueries, assuming row order is stable. It isn't. SQL tables have no guaranteed row order unless you specify one. Always use a value-based comparison (<= current_value) rather than relying on physical row position.
What happens if two rows have the same order_month value? With the <= approach, the correlated subquery for January would sum both January rows, not just the one it's currently computing. If your ordering column has duplicates and you GROUP BY it (so there should only be one row per value), this isn't an issue. But if you're computing running totals on an ungrouped table with repeated dates, you'll get incorrect results.
Diagnosis: Check whether your running total appears to "double-count" some periods.
Fix: Ensure your data is pre-aggregated to one row per ordering value before applying the running total pattern. Or add additional conditions to break ties.
Using < instead of <= causes the current row to be excluded from its own cumulative total. Your running total for March would only include January and February — it would never catch up to the complete picture.
-- WRONG: < excludes the current row
WHERE m2.order_month < m.order_month
-- CORRECT: <= includes the current row
WHERE m2.order_month <= m.order_month
This is easy to catch: the first period's cumulative total will be zero (or NULL), and every period will appear to be lagging one period behind.
If your running total query runs fine on 6 months of data but times out on 3 years, you've hit the O(N²) wall. The fix for production systems with large datasets is to use window functions (SUM(...) OVER (ORDER BY ...)), which most modern databases compute in a single pass. The subquery approach taught here is valuable for learning the logic, for databases without window function support, and for cases where the dataset is small enough that simplicity beats optimization.
Tip
If you're working with a database that supports window functions (PostgreSQL, SQL Server, MySQL 8+, BigQuery, Snowflake, DuckDB), you should generally prefer them for running totals over large datasets. The subquery approach taught in this lesson gives you the conceptual foundation to understand what window functions are doing under the hood, which makes them far less magical when you encounter them.
You now have two reliable techniques for calculating running totals and cumulative sums using core SQL tools:
Correlated subquery in SELECT: For each outer row, a subquery re-executes with a <= comparison on the ordering column to sum all prior rows. Add matching conditions for each grouping dimension (category, rep, region) to partition the running total correctly.
Self-join approach: Join a grouped dataset to itself on m2.period <= m1.period, then GROUP BY the outer row's identifier and SUM the joined values. Produces the same results with slightly different readability characteristics.
Both approaches are O(N²) in complexity, which makes them appropriate for moderate-sized datasets (hundreds to low thousands of distinct periods). For high-volume production analytics, window functions are the right tool.
The deeper skill here is the correlated subquery pattern itself. You'll use this same structure for a range of "for each row, look at related rows" problems: finding the previous row's value, computing a running maximum, checking whether a condition has been true at any prior point. The running total is the most common form, but the pattern generalizes widely.
For deeper work with subqueries and how to structure complex analytical queries, explore Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture. If you want to go further with aggregate functions — especially HAVING clauses and conditional aggregation — Master SQL Aggregate Functions: Advanced GROUP BY, HAVING, and Performance Optimization is a natural next step.
Finally, if your work involves comparing one period to a previous one (month-over-month growth rather than cumulative totals), Using SQL to Compare Current and Prior Period Results: Date Filtering, Self-Joins, and Conditional Aggregation in Practice covers that closely related pattern.