Running totals in SQL: SUM() OVER, and the frame clause that trips people up

A running total — cumulative sales, a balance that grows day over day, a leaderboard score over time — is one of the first window-function questions most interview loops ask, because it's short to state and has a well-known trap in it.

The pattern

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

No GROUP BY, no self-join. SUM(...) OVER (ORDER BY sale_date) computes, for every row, the sum of amount over every row from the start of the ordering up to and including the current one — that's the definition of a running total. The outer ORDER BY is there for display; the ORDER BY inside OVER is what actually drives the accumulation and would still be required even if you didn't care about output order.

Why this differs from a plain aggregate

GROUP BY collapses rows into one row per group. A window function does the opposite: every input row survives in the output, each carrying a value computed over a window of related rows. That's what makes it the right tool here — you want one row per day, each showing the sum-so-far, not one row per whole table.

The trap: the default frame

Here's the part that catches people who've memorized the syntax without understanding it. SUM(amount) OVER (ORDER BY sale_date) has an implicit frame clause you didn't write:

SUM(amount) OVER (
  ORDER BY sale_date
  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

The default frame is RANGE, not ROWS. RANGE groups by the value being ordered on, not the row's position — so if two rows share the same sale_date, RANGE treats them as the same point and gives both of them the combined running total as of that date, not two separate partial sums. For a DATE column with one row per day this never bites, because dates in the demo data don't repeat. It bites the moment the same query is applied to a table with duplicate order-by values — two orders placed the same second, two transactions on the same day — and the running total silently double-counts or under-counts depending on which side of the tie a row is read on.

The fix, when ties are possible, is to be explicit about wanting row-by-row accumulation:

SUM(amount) OVER (
  ORDER BY sale_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

ROWS counts physical rows, not tied values, so each row gets its own distinct partial sum regardless of ties on the ordering column. As a rule: if the column you're ordering by can repeat, write ROWS explicitly rather than relying on the default.

Same shape, different aggregate

Once the pattern clicks, it's not just SUM. A running count is COUNT(*) OVER (ORDER BY ...), a running max is MAX(...) OVER (ORDER BY ...), and partitioning it per group (a running total per customer, say) is one more clause: SUM(amount) OVER (PARTITION BY customer_id ORDER BY sale_date).

More in Window functions.