Common mistake

SQL: Window Running Totals Can Jump on Ties

The default RANGE frame can include tied ORDER BY rows together. Use ROWS when you need a row-by-row running total.

The symptom

You write a running total with sum(...) over (order by ...). Most of it looks plausible, but every row that shares a sort value with its neighbors shows the same figure, and the total advances in jumps rather than one row at a time.

Why it happens

When an ordered aggregate window does not name a frame, the SQL standard supplies one, and that default is built on RANGE. A RANGE frame is defined by values, not positions: rows that tie on the ORDER BY expression are peers, and a frame that reaches any peer reaches all of them. So a running total over a column with repeats absorbs the whole tied group the moment it reaches the first member.

That behavior is valid SQL and occasionally what you want. It is almost never what people mean by a row-by-row running total.

The fix

Name the frame explicitly. ROWS counts physical rows in the sorted result rather than matching on values, so the total advances one row at a time however many ties the sort column holds. Both queries below run a total over Garden Shop products in category order, and eight categories cover 24 products.

Wrong: default RANGE frame

select product_id,
       category_id,
       price,
       sum(price) over (order by category_id) as running_total
from products
order by category_id, product_id

Right: explicit ROWS frame

select product_id,
       category_id,
       price,
       sum(price) over (
         order by category_id, product_id
         rows between unbounded preceding and current row
       ) as running_total
from products
order by category_id, product_id

The first five products all sit in category 1, so under the default frame they are peers and every one of them reads 114, the whole category's price total, before the window moves on. The ROWS frame walks the same five rows one at a time (32, 60, 78, 90, 114) and only agrees with the default on the last row of each tied group.

Tie-breakers still matter

Add a deterministic tie-breaker such as product_id when the first sort column can repeat, as the fixed query does. ROWS counts positions, so the answer is only reproducible if the row order is settled before the frame is applied.