Why does my running total in SQL repeat numbers?
Watch two same-day orders get the same running total, understand the RANGE frame, and fix it with ROWS.
Free course. Your Maestro, an AI, .
Run the exercise code below as it is. It's the running total of Elena's orders, using the recipe from the previous page. Before you look at the result, notice February 6, 2025: Elena placed two orders that day, 1014 (2390 pesos) and 1015 (1580 pesos). Through the day before, she was at 4080. Answer the question below.
Check yourself: With SUM(monto) OVER (PARTITION BY cliente ORDER BY fecha), what running total do Elena's orders 1014 and 1015, both from February 6, show?
Here's what Maestro's sandbox returned:
| pedido_id | fecha | monto | acumulado |
|---|---|---|---|
| 1009 | 2025-01-23 | 1280 | 4080 |
| 1014 | 2025-02-06 | 2390 | 8050 |
| 1015 | 2025-02-06 | 1580 | 8050 |
| 1028 | 2025-02-21 | 890 | 8940 |
Both orders show the same running total. It's not a bug. It's the default frame rule.
When you write ORDER BY inside OVER and say nothing about the frame, DuckDB uses this one:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"From the start of the partition to the current row." The catch is what "current row" means:
- With
RANGE, the current row includes its ties: every row with the same value in theORDER BY. For order 1014, "through the current row" already includes 1015, because they share a date. - With
ROWS, the current row is just that row.
The diagram sums up when it matters.
Are there ties in the OVER's ORDER BY?
Path 1 of 2: no
ROWS and RANGE give the same result
every row is unique in that order
Ends here.
Path 2 of 2: yes
RANGE adds tied rows together
ROWS moves one row at a time
Use ROWS and add a tiebreaker
for example, pedido_id
Ends here.
For the running total to move order by order, you need two things:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so each row adds up only through itself.- A tiebreaker in the
ORDER BY(fecha, pedido_id). Without it,ROWSmoves row by row, but nothing says which of the two same-day orders comes first.
Try it
Fix the query below so orders 1014 and 1015 get different running totals. If you switch to ROWS and it still doesn't add up, ask your Maestro.
Change the OVER so Elena's running total moves one order at a time: write the frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and break ties with pedidoid. Keep the same columns and the same final sort. You're done when order 1014 shows 6470 and 1015 shows 8050.
Your turn
Change the OVER so Elena's running total moves one order at a time: write the frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and break ties with pedido_id. Keep the same columns and the same final sort. You're done when order 1014 shows 6470 and 1015 shows 8050.
Tables: pedidos (pedido_id, cliente, fecha, monto)
pedidos · 60 rows
| Column | Type |
|---|---|
| pedido_id Número del pedido; crece con el orden en que se registró. | BIGINT |
| cliente Nombre del cliente. | VARCHAR |
| fecha Día del pedido, sin hora. | DATE |
| monto Monto del pedido, en pesos. | BIGINT |
First rows
| pedido_id | cliente | fecha | monto |
|---|---|---|---|
| 1001 | Fer | 2025-01-03 | 850 |
| 1002 | Diego | 2025-01-04 | 1360 |
| 1003 | Beto | 2025-01-11 | 600 |
Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.
Ready to run your query.
Which one is "right"? It depends on the question. If you want the running total at the end of each day, the default RANGE is exactly what you need: both February 6 orders show what Elena had when that day closed. If you want the running total after each order, use ROWS with a tiebreaker. What matters is that you make the choice, instead of letting the default rule make it for you.
Interviews almost never ask "what is RANGE?" Sometimes they hand you a table of transactions with several per day, ask for "the balance after each transaction," and watch whether your running total repeats numbers. Another version: "why do these two queries return different results?", where one sorts by date and the other by date and id. If you explain the default frame and write it out explicitly, you've answered it. If you want your Maestro to give you the balance version, ask.
Check your own queries: look for every OVER (... ORDER BY ...) that uses SUM, AVG or COUNT with no frame written. If the ORDER BY column can repeat (dates without a time, for example), decide whether you want the end of the day or the value after each row, and write the frame. PostgreSQL documents the same default rule: with ORDER BY, the frame runs through the current row plus any rows tied with it.