How Do You Calculate a Running Total in SQL?

The one-line answer

A running total is SUM() used as a window function instead of a GROUP BY aggregate: add an OVER (ORDER BY ...) clause and it accumulates instead of collapsing the rows.

That's the whole trick for most running totals: no GROUP BY, no self-join, no correlated subquery. But run that exact query and look closely at February 14th and April 1st. Two different orders, same date, and both rows show the same running total, as if the second order never happened. That's not a bug in this dataset, it's the documented default behavior of SUM() OVER (ORDER BY ...), and it's the part every other running-total tutorial skips because their sample data happens to have a unique sort key.

Why tied dates break the running total

The OVER (ORDER BY ...) clause doesn't just define a sort order, it also defines a frame: the set of rows each row's calculation includes. When you don't write a frame explicitly, SQL doesn't default to "every row before this one." It defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and under RANGE, "current row" means every row that ties with it on the ORDER BY column, not just the physical row. Two orders on 2022-02-14 are peers, so both get the combined total through the end of that date, instead of one getting the total up to itself and the other getting one row more.

See it in isolation by computing the running total first, in a CTE, then pulling out just the tied dates afterward. Filtering after the window function runs (instead of in a WHERE on the base tables) keeps the running total anchored to the whole table, which is what actually ships in a report:

Both February 14th rows read 821.39, the running total through the end of that date, and both April 1st rows read 1437.32. If you were expecting the running total to grow by each order's own amount on top of the last, it looks like one order in each pair silently vanished.

The fix: say ROWS, not RANGE

If you want a literal row-by-row accumulation, where each row adds exactly its own amount regardless of what it ties with, write the frame yourself with ROWS instead of letting RANGE default in:

Now the two February 14th rows read 786.44 and then 821.39, one order at a time, and the two April 1st rows read 1373.36 and then 1437.32. Two things changed, and both matter: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW forces strict per-row accumulation, and , o.id was added to the ORDER BY so the split between the tied rows is deterministic instead of whatever order the engine happens to scan them in. ROWS alone fixes the frame, not the tie-break; without the extra sort column, which tied row gets the smaller number is unspecified, and a plan change could flip it.

Running totals per group with PARTITION BY

Add PARTITION BY to restart the total for each group, the same way GROUP BY would restart an aggregate. Here's each customer's spend accumulating independently:

Customer 1's total climbs across their three orders, then customer 2 starts over from zero. PARTITION BY doesn't change the frame rules above, it just runs the same windowed calculation separately inside each group, so the same RANGE-versus-ROWS gotcha applies per customer too, not just once across the whole table.

Getting the total before this row

Sometimes you want the balance going into an order, not including it, like showing how much had already accumulated before this particular purchase. Shift the frame's end back one row with 1 PRECEDING:

The very first row has nothing before it, so total_before_this_order comes back NULL there, not zero. The frame is genuinely empty for that row, and SUM over an empty set is NULL, the same rule that makes SUM on a table with zero matching rows return NULL instead of 0. Wrap it in COALESCE(..., 0) if a blank first row would confuse whoever reads the report.

Filtering on a running total

A window function can't go in WHERE, because WHERE is evaluated before window functions run. If you need "the first order where cumulative revenue passed $1,000," compute the running total first and filter in an outer query:

A CTE reads a little cleaner than a subquery here if you prefer one; either way, the window function has to finish computing the column before anything can filter on it. The CTE vs subquery post covers when each form is the better choice.

Where to go next

The running total example walks the basic SUM() OVER (ORDER BY ...) pattern on its own if you want a shorter reference without the frame details above. For the window function fundamentals, PARTITION BY, and how a window differs from a GROUP BY aggregate, see the Window Functions lesson and the SUM() reference page. To practice spotting tied sort keys before they cause this, the practice problems are a good place to write a running total over data you've deliberately seeded with a duplicate date and check whether your frame handles it the way you expect.