Write a SQL Query That Builds a Running Total Without Window Functions

Window functions like SUM() OVER (ORDER BY ...) make calculating running totals in SQL straightforward and efficient. But what happens if you’re working on a legacy system or a lightweight database like MySQL 5.x or SQLite that doesn’t support window functions? In this article, we’ll explore how to calculate running totals using classic SQL constructs like subqueries and self-joins — no window functions required.

1. What Is a Running Total and Why Use One?

A running total is a cumulative sum where each row’s value is the sum of itself and all the previous rows based on a specified order. Running totals are especially useful for financial reports, sales dashboards, time-series analytics, and anywhere we need progressive aggregation.

Here’s a sample table of sales data we’ll use throughout this article:

CREATE TABLE sales (
    id INT PRIMARY KEY,
    sales_date DATE,
    amount INT
);

INSERT INTO sales (id, sales_date, amount) VALUES 
(1, '2023-01-01', 100),
(2, '2023-01-02', 150),
(3, '2023-01-03', 200),
(4, '2023-01-04', 175);

Our goal is to create a result like this:

+------------+--------+
| sales_date | running_total |
+------------+--------+
| 2023-01-01 |    100 | 
| 2023-01-02 |    250 | 
| 2023-01-03 |    450 | 
| 2023-01-04 |    625 |
+------------+--------+

2. Approach Using a Correlated Subquery

The simplest window-free solution uses a correlated subquery, which calculates a cumulative sum by summing values for all rows with earlier or equal dates.

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

How this works: For each row in the outer query (aliased as s1), the inner query sums up amount from s2 where s2.sales_date is less than or equal to s1.sales_date. This ensures the cumulative total includes the current and earlier entries.

Performance Tip: On large datasets this gets expensive (O(n²)) because the subquery runs for every row. Index your date column to help the performance.

3. Optimizing With a Self Join

If correlated subqueries slow you down, another approach is to use an explicit self-join to mimic the same logic more efficiently:

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

Explanation: For each s1 row, join all s2 rows where the date is before or equal to s1.sales_date. Then we just sum the s2.amount to get the running total.

Real-World Tip: While still O(n²), the optimizer might handle the join more efficiently than nested subqueries. Always check execution plans if performance is a concern.

4. Enhance Ordering With Row IDs

Sometimes datetime alone isn’t sufficient to define a row’s order. You can use an auto-incremented ID to strengthen ordering logic:

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

Use case: If multiple sales happen at the same time, using id ensures deterministic results — no ambiguity about order.

Tip: Always define clear ordering criteria for running totals to avoid incorrect aggregations and potential surprises in results.

5. Simulating Window-Like Behavior With Temporary Tables

In systems that allow session-level variables (like MySQL), you can simulate running totals imperatively. This example uses variables in MySQL:

SET @running_total := 0;

SELECT
    sales_date,
    amount,
    (@running_total := @running_total + amount) AS running_total
FROM sales
ORDER BY sales_date;

Why this works: You sort the data first, and then use a session variable @running_total to iteratively build the cumulative sum row-by-row.

Performance: Very fast and O(n), but database-specific (won’t work in PostgreSQL or SQLite). Use when you know your DB engine supports variables.

Conclusion

Even without modern SQL features like window functions, traditional SQL constructs provide creative and workable solutions for calculating running totals. Whether via correlated subqueries, self-joins, or session variables, each technique has trade-offs in performance and portability.

When working in constrained environments, these approaches help you deliver accurate analytic results using nothing but ANSI SQL fundamentals. For best results, combine these techniques with proper indexing and careful ordering logic.

Running totals are foundational to many analyses — and with these patterns in your toolkit, you can build them almost anywhere.

Useful links: