SQL / WINDOW FUNCTIONS
Running totals with ordered window sums
Compute per-row running totals with SUM(x) OVER (ORDER BY k), restart them per group, and control what happens when the ordering key has ties.
What you will learn
- Write SUM(x) OVER (ORDER BY k) for a cumulative value instead of one repeated total
- Restart the accumulation per group by adding PARTITION BY to the same OVER clause
- Explain why rows tied on the window ORDER BY key all show the same running total
- Force one row at a time with a unique sort key or ROWS UNBOUNDED PRECEDING
Understanding Running totals with ordered window sums
SUM(amount) OVER (ORDER BY sale_day) is not a sorted grand total; it is a different number on every row. The ORDER BY inside OVER gives the window a default frame of everything from the start of the partition through the current row, so as you move down the ordered rows the frame grows and the sum grows with it. SUM(amount) OVER (), with no ORDER BY, leaves the frame at the whole partition and repeats a single value on every row. The mental model is a shade pulled down one row at a time: each row sums its own prefix of the ordering.
Two different ORDER BY clauses are in play and they do different jobs. The one inside OVER defines what "so far" means and is the only one that changes the numbers; the one at the end of the query only decides the order the rows are printed in. Drop the outer one and the running totals are still computed against the window ordering, but they can arrive interleaved, so the column no longer looks like it is climbing. Keeping the two in agreement is what makes a running total readable.
The default frame is expressed in RANGE terms, which compares ordering values rather than counting positions, and "current row" under RANGE means the last of the rows sharing the current value. So if three sales share the date 2024-03-02, all three report the total through the end of that day and the per-row increments vanish. There are two fixes: make the window ORDER BY unique by adding a tiebreaker such as the primary key, or ask for ROWS so the frame advances one physical row at a time. Which one you want depends on whether the question is "balance at the end of the day" or "balance after each transaction".
WITH sales(sale_day, amount) AS (
VALUES ('2024-03-01', 120),
('2024-03-02', 80),
('2024-03-03', 200),
('2024-03-04', 50)
)
SELECT sale_day,
amount,
SUM(amount) OVER (ORDER BY sale_day) AS running_total,
SUM(amount) OVER () AS grand_total
FROM sales
ORDER BY sale_day;Putting ORDER BY inside OVER changes an aggregate's frame to everything up to the current row, and that growing frame is what turns SUM into an accumulation.
Worked examples
Restarting the accumulation per group
Shows a running total that begins again for each region instead of carrying across regions.
WITH sales(region, sale_day, amount) AS (
VALUES ('east', '2024-03-01', 120),
('east', '2024-03-02', 80),
('west', '2024-03-01', 40),
('west', '2024-03-02', 200),
('west', '2024-03-03', 10)
)
SELECT region,
sale_day,
amount,
SUM(amount) OVER (PARTITION BY region ORDER BY sale_day) AS region_total
FROM sales
ORDER BY region, sale_day;Example explained
Line 1PARTITION BY region resets the frame at each region boundary, which is why west starts at 40 and not at 240.
Line 2ORDER BY sale_day inside the same OVER decides which rows of that region count as "so far".
Line 3The last row of each region equals that region's own total, a quick check that the frame ends at the current row.
Line 4The outer ORDER BY region, sale_day only arranges the printout; removing it would not change any region_total value.
What happens when the ordering key repeats
Compares the default RANGE behaviour against a row-by-row accumulation when two rows share a date.
WITH tx(sale_day, amount) AS (
VALUES ('2024-03-01', 50),
('2024-03-02', 30),
('2024-03-02', 70),
('2024-03-03', 10)
)
SELECT sale_day,
amount,
SUM(amount) OVER (ORDER BY sale_day) AS range_default,
SUM(amount) OVER (ORDER BY sale_day, amount
ROWS UNBOUNDED PRECEDING) AS row_by_row
FROM tx
ORDER BY sale_day, amount;Example explained
Line 1range_default has no explicit frame, so both 2024-03-02 rows are peers on the ordering value and each reports 150, the total through the end of that date.
Line 2Adding amount to the window ORDER BY makes every key unique, and ROWS UNBOUNDED PRECEDING means "from the first row up to this one row".
Line 3row_by_row therefore steps 50, 80, 150, 160, so each row's rise equals exactly its own amount.
Line 4Both columns agree again at 160 once the tie group is finished; only the rows inside the tie differ.
Running balance and share of the final total
Accumulates signed amounts into a balance and divides it by an unordered window sum to get a cumulative percentage.
WITH ledger(sale_day, delta) AS (
VALUES ('2024-03-01', 500),
('2024-03-02', -120),
('2024-03-03', -80),
('2024-03-04', 200)
)
SELECT sale_day,
delta,
SUM(delta) OVER (ORDER BY sale_day) AS balance,
ROUND(100.0 * SUM(delta) OVER (ORDER BY sale_day)
/ SUM(delta) OVER (), 1) AS pct_of_final
FROM ledger
ORDER BY sale_day;Example explained
Line 1Nothing about the accumulation assumes positive values, so negative deltas pull the running sum down and it reads as a balance after each entry.
Line 2SUM(delta) OVER () has no ORDER BY, so it holds the 500 closing figure on every row and makes a stable denominator.
Line 3The 100.0 literal forces decimal division; writing 100 * ... / ... would truncate to whole numbers in engines with integer division.
Line 4The balance is not monotonic here, which is a reminder that a running total climbs only when the accumulated values do.
Important notes
SUM skips NULLs, so a NULL amount leaves the running total flat; but if every row up to that point is NULL the result is NULL rather than 0, so wrap it in COALESCE(..., 0) when a report needs a number.
ORDER BY sale_day DESC inside OVER accumulates from the newest row backwards, giving a remaining-total; if the very first printed row already shows the grand total, check the direction of the window ordering.
Common mistakes
Sorting the query with a trailing ORDER BY sale_day but leaving OVER () empty: every row shows the same grand total, because the outer sort never touches the window frame.
Assuming SUM(amount) OVER (ORDER BY sale_day) adds one row at a time when a date repeats: tied rows share that date's closing total, so one row appears to add nothing and the next appears to jump twice.
Adding WHERE sale_day >= '2024-03-02' and expecting the opening balance to carry forward: WHERE runs before the window, so the excluded rows never enter the sum and the first running total starts too low.
Try it yourself
Change, predict, then run
In a browser editor, build a four-row VALUES list of deposits in which two rows share the same date, then add two columns: SUM(amount) OVER (ORDER BY the_date) and the same sum with a unique tiebreaker appended to the window ORDER BY. Compare the two tied rows and state which column lets you recover each row's own amount as an increment.
Open the SQL workspaceCheck your understanding
A table holds three rows: 2024-05-01 with amount 100, and two rows both dated 2024-05-02 with amounts 30 and 70. What does SUM(amount) OVER (ORDER BY sale_date) return on the two 2024-05-02 rows?
- 200 on both rows, because the default frame is a RANGE frame and rows with equal ordering values are treated as one peer group
- 130 and 200, because the frame extends by one row each time and the tie is broken by physical row order
- 100 on both rows, because rows tied with the current row are excluded from its own frame
- 130 and 200 by default, and 200 on both only if you write ROWS UNBOUNDED PRECEDING
Show answer
With no explicit frame the window is RANGE from the start of the partition through the current row, and under RANGE the current row includes every row sharing its ordering value, so both 2024-05-02 rows sum 100 + 30 + 70 = 200. The 130 and 200 answer is tempting because it is what a row-counting frame would give, but that requires explicitly asking for ROWS (and a deterministic tiebreaker), which reverses the roles in the last option.