Calculating Running Totals with Window Functions

1. What a running total is and where it is needed

A running total is a cumulative sum that, for every row of a sorted result set, contains the sum of all values from the first row up to the current row. Unlike a simple grand total, which shows the same value on every row, a running total grows with every additional row. Classic use cases are account statements, where every transaction shows the current balance after that transaction, cumulative revenue reports per month, or the ongoing inventory development of a warehouse over time.

Before window functions were widely available in relational databases, calculating a running total in plain SQL was cumbersome and usually relied on correlated subqueries or procedural cursor logic. Both approaches are error prone, hard to read, and slow with larger data volumes. With SUM() OVER, a running total can today be calculated in a few lines of readable SQL, without burdening the database with inefficient repeated partial calculations.

The term running total is often used interchangeably with cumulative sum, and both terms refer to the same concept: a continuously accumulating calculation over a defined order of rows. What matters for a correct running total is always a unique, stable sort order, because without it there is no clear meaning to "up to the current row" in the first place.

2. SUM() OVER for cumulative sums: basic syntax

The basic syntax for a running total is refreshingly simple: SUM(column) OVER (ORDER BY sort_column). The aggregate function SUM becomes a window function through OVER, and the ORDER BY inside the parentheses defines the order in which the values are accumulated. It is important that this ORDER BY is distinct from any final ORDER BY of the whole query, even though both often use the same column.

As soon as ORDER BY is specified inside OVER, the implicit window behavior automatically changes: instead of the entire result set, only the range from the first row up to the current row is used for the sum. That is exactly the behavior that defines a running total, and it happens automatically without any additional frame specification as soon as an ORDER BY is present.

-- Basic running total: cumulative sum of transactions by date
SELECT
    transaction_date,
    amount,
    SUM(amount) OVER (ORDER BY transaction_date) AS running_balance
FROM transactions
ORDER BY transaction_date;

-- Result
-- transaction_date | amount | running_balance
-- 2026-07-01       |    500 |             500
-- 2026-07-03       |    250 |             750
-- 2026-07-05       |   -100 |             650
-- 2026-07-08       |    400 |            1050

3. The default frame: RANGE UNBOUNDED PRECEDING

Behind the seemingly simple behavior of SUM() OVER (ORDER BY ...) lies an implicit frame definition that is worth knowing to truly understand running totals. As soon as ORDER BY is present inside the OVER clause but no explicit frame is specified, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW applies by default. UNBOUNDED PRECEDING means "from the start of the partition", CURRENT ROW means "up to and including the current row". Exactly this range is used for the sum calculation.

The default frame uses RANGE instead of ROWS, which can lead to behavior that is not immediately expected when there are tied values in the ORDER BY: all rows with the same sort value get the same running total value, namely the sum up to and including all tied rows, not just up to their own row. Anyone who wants guaranteed row-by-row behavior, even on ties in the sort value, should explicitly set the frame to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to bypass this RANGE behavior.

4. Resetting running totals per group with PARTITION BY

In practice, a running total is rarely needed globally across an entire table, but usually per customer, per account, or per category. With PARTITION BY inside the same OVER clause, this behavior can be represented exactly: SUM(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date) computes an independent running total for each customer, which automatically starts back at zero for every new customer.

This automatic reset is not extra logic that has to be explicitly programmed, it is a direct consequence of PARTITION BY treating each group as an independent computation unit for the window function. Before window functions, this reset had to be reproduced with a WHERE condition inside a correlated subquery, which also had to check customer membership in addition to the sort condition, making the query considerably more complex and slower.

-- Running total per customer, resets automatically for each new customer
SELECT
    customer_id,
    order_date,
    order_amount,
    SUM(order_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS customer_running_total
FROM orders
ORDER BY customer_id, order_date;

-- Result
-- customer_id | order_date | order_amount | customer_running_total
-- 101         | 2026-06-01 |          200 |                    200
-- 101         | 2026-06-15 |          150 |                    350
-- 102         | 2026-06-02 |          300 |                    300   -- reset for new customer
-- 102         | 2026-06-20 |          120 |                    420

5. The classic approach: correlated subquery

Before window functions became widely available, a running total was typically calculated with a correlated subquery: for every row of the outer query, an inner subquery sums all rows whose sort column is less than or equal to the value of the current row. This approach works correctly, but is structurally fundamentally different from a window function, because the subquery gets executed completely anew for every single row of the outer query.

With n rows, that means, in the worst case, n separate aggregations over an average of n/2 rows, leading to quadratic complexity. On small tables with a few hundred rows, this difference is barely measurable, but on tables with hundreds of thousands or millions of rows, the correlated subquery quickly becomes a performance bottleneck that can slow a query down from milliseconds to several seconds or minutes.

-- Classic correlated subquery approach (avoid for large tables)
SELECT
    t1.transaction_date,
    t1.amount,
    (
        SELECT SUM(t2.amount)
        FROM transactions t2
        WHERE t2.transaction_date <= t1.transaction_date
    ) AS running_balance
FROM transactions t1
ORDER BY t1.transaction_date;

-- Equivalent, much faster window function version
SELECT
    transaction_date,
    amount,
    SUM(amount) OVER (ORDER BY transaction_date) AS running_balance
FROM transactions
ORDER BY transaction_date;

6. Performance comparison: window function vs. subquery

The performance difference between a correlated subquery and a window function for running totals is one of the clearest and best documented in SQL optimization. While the correlated subquery reads and re-sums the data for every single row, the database sorts the data once for the window function and accumulates the sum in a single pass over the sorted data. This algorithmic difference corresponds to the difference between O(n squared) and O(n log n), or even O(n) when the sort order is already provided by a matching index.

In practice, this means: on a table with 100000 rows, a correlated subquery can take several seconds or even minutes, while the same calculation with SUM() OVER typically completes in a fraction of a second. This difference is one of the strongest practical arguments for systematically migrating existing legacy SQL code with correlated subqueries for running totals to window functions, as soon as the database version in use supports them.

7. Moving sums with ROWS BETWEEN

Besides the classic running total, which always sums from the start of the partition, there is the moving sum, which only includes a fixed number of preceding rows. With ROWS BETWEEN n PRECEDING AND CURRENT ROW, you can, for example, calculate a sum over the last seven days, which removes the oldest row from the window with every new row. This technique is frequently used for moving averages and short-term trend analyses, where a value too far in the past would dilute the timeliness of the metric.

The difference from the classic running total lies solely in the frame: while the default frame uses UNBOUNDED PRECEDING and therefore reaches back indefinitely, ROWS n PRECEDING restricts the window to a fixed number of rows. Both variants use the same SUM() OVER syntax, but differ in how far the window reaches into the past, which allows a choice between a cumulative overall perspective and a short-term, moving trend.

-- Moving sum: last 7 rows only, not the entire history
SELECT
    sale_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS revenue_last_7_days
FROM daily_sales
ORDER BY sale_date;

8. Running totals with additional conditions

A common practical requirement is a running total that only includes certain rows, for instance only confirmed orders or only rows with a positive amount. Since a window function itself cannot contain a WHERE clause, this problem is solved either with a conditional expression inside SUM, such as SUM(CASE WHEN status = 'confirmed' THEN amount ELSE 0 END) OVER (...), or with an upstream WHERE clause that filters the source data before the window function sees it.

The difference between the two approaches matters: an upstream WHERE clause removes the rows entirely from the result set before the window function processes them, while the CASE expression inside SUM keeps all rows in the output, but only lets certain values contribute to the sum. Which approach is correct depends on whether the excluded rows should remain visible in the result or not.

-- Running total that only counts confirmed orders
SELECT
    order_date,
    status,
    amount,
    SUM(
        CASE WHEN status = 'confirmed' THEN amount ELSE 0 END
    ) OVER (ORDER BY order_date) AS confirmed_running_total
FROM orders
ORDER BY order_date;

9. Common pitfalls with running totals

The most common mistake with running totals is a non-unique sort order in the ORDER BY of the OVER clause. When several rows share the same sort value, for instance the same date without a time component, the default RANGE frame can cause all those rows to receive the same running total value, which often does not match business expectations. An additional unique column in the ORDER BY, for instance a sequential ID as a second sort criterion, or an explicit ROWS frame reliably solves this problem.

A second common mistake is forgetting PARTITION BY when a running total is actually supposed to be calculated per group. Without PARTITION BY, the window function sums across the entire result set, which, for an analysis that is actually meant to run per customer or per account, leads to completely wrong, far too high values. A third pitfall concerns NULL values: SUM automatically ignores NULL values, which can, with insufficient data quality checks, cause missing values to be silently treated as zero instead of surfacing as erroneous or incomplete data.

10. Summary

Running totals can be calculated directly in SQL with SUM() OVER, without resorting to application logic or slow correlated subqueries. The basic syntax SUM(column) OVER (ORDER BY sort_column) uses the implicit RANGE UNBOUNDED PRECEDING frame to compute a cumulative sum from the first row up to the current row. PARTITION BY extends this calculation with an automatic reset per group, while ROWS BETWEEN enables moving sums over a fixed window.

The performance difference from a correlated subquery is substantial on larger tables and often the decisive reason to modernize existing legacy code. Anyone who knows the typical pitfalls, especially the need for a unique sort order and PARTITION BY for grouped calculations, can use running totals confidently and performantly for account statements, revenue reports, and similar cumulative analyses.

11. FAQ: running totals with window functions