Español

Field manual · SQL traps

Why does the running balance repeat the exact same number on two different rows?

sum(...) OVER (ORDER BY date) with no explicit frame defaults to RANGE: it groups every row with the same date into one block, and all of them show the same running total.

Where it shows up

Any running balance, cumulative total or ranking computed with a window function when the ORDER BY column has ties (two transactions the same day, two sales at the same hour).

Example

Wrong: no explicit frame, ties share a row

SELECT date, amount,
       sum(amount) OVER (ORDER BY date) AS balance
FROM transactions;
-- two entries on the same day fall into the same RANGE:
-- both show the balance after adding both of them, neither shows the balance in between

Right: ROWS, which counts rows and not tied values

SELECT date, amount,
       sum(amount) OVER (
         ORDER BY date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS balance
FROM transactions;

When a window function has an ORDER BY but no explicit frame, the engine defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE groups by *value*, not by row: every row sharing the same ORDER BY value lands at the same point in the frame, so all of them see the same sum, the one after adding all of them. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW moves row by row regardless of ties: the first of two same-day entries sees the balance before the second is added, and the second sees the balance with both. The rule of thumb: if the word is 'running balance' or 'cumulative through this row', you almost always want ROWS, not the RANGE you get by default without asking for it.

This trap is hiding inside a real case file, with real data.

Play the first case free →

Related traps