The Running Total That Wasn't: A Window Function Gotcha

The query almost everyone writes first

You've got a table of daily numbers (sales, signups, whatever) and you want a running total: each row showing the cumulative sum up to that point. Window functions are built for exactly this, so the natural first attempt looks like:

SELECT day, amount, SUM(amount) OVER () AS total
FROM daily_sales
ORDER BY day;

SUM() OVER () is a window function. No GROUP BY needed, it keeps every row. It runs. It returns a number in every row. It looks like a running total.

What it actually returns

 day | amount | total
-----+--------+-------
   1 |    100 | 660.0
   2 |    150 | 660.0
   3 |    120 | 660.0
   4 |    200 | 660.0
   5 |     90 | 660.0

Every single row shows 660, the sum of the whole table, repeated on every line. Not a running total. Not even close to one. And critically: no error, no warning, a plausible-looking number in every row. If you're not specifically expecting a running total and checking each value, this passes a glance test completely.

Why

OVER () with nothing inside the parentheses defines a window with no ordering and no frame. That means "the entire result set, as one group." SUM() computed over that window is just the total, computed once, stamped onto every row. There's no notion of "up to this point" anywhere in that query, because nothing told the window what "this point" means.

The fix is one clause, and it changes the meaning of the window entirely, not just its output:

SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total
FROM daily_sales
ORDER BY day;

 day | amount | running_total
-----+--------+---------------
   1 |    100 |          100.0
   2 |    150 |          250.0
   3 |    120 |          370.0
   4 |    200 |          570.0
   5 |     90 |          660.0

Adding ORDER BY day inside OVER (...) does two things at once, and the second one is the part that trips people up. It defines an order for the window. As a side effect most people never read about explicitly, it also changes the default frame, from "the whole partition" to "from the start of the partition up to the current row." That second, implicit change is the entire difference between the two queries above. The ORDER BY isn't just sorting; it's silently redefining how much of the table each row's calculation sees.

One level deeper: what happens with a tie

Real data has ties. Two days with the identical amount is a completely ordinary thing to happen. What does the running total do then?

-- day 2 and day 3 both have amount = 150
SELECT day, amount, SUM(amount) OVER (ORDER BY amount) AS running_total
FROM daily_sales
ORDER BY day;

 day | amount | running_total
-----+--------+---------------
   1 |    100 |          100.0
   2 |    150 |          400.0
   3 |    150 |          400.0
   4 |    200 |          600.0

Both tied rows show 400, the sum through both of them, not a genuine row-by-row progression. That's not a bug; it's the default frame type (RANGE) treating rows with equal ORDER BY values as one logical group, or "peers," and giving every peer the same cumulative value. If you actually need a strict row-by-row running total (the specific row's own position in physical order, ties or not), you have to ask for it explicitly with ROWS instead of the default:

SELECT day, amount,
       SUM(amount) OVER (
           ORDER BY amount
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM daily_sales
ORDER BY day;

 day | amount | running_total
-----+--------+---------------
   1 |    100 |          100.0
   2 |    150 |          250.0
   3 |    150 |          400.0
   4 |    200 |          600.0

Now day 2 and day 3 genuinely differ: 250 then 400. ROWS counts physical rows one at a time regardless of ties, where RANGE (the silent default) counts logical peer-groups.

The takeaway

Three real, distinct behaviors live behind what looks like one syntax:

None of these error on the others' data. All three return a plausible-looking number. The only way to know which one you actually wrote is to know that ORDER BY inside OVER (...) does two jobs at once. Check a tied row by hand at least once before trusting the result.