SQL: window functions for running totals and ranks
Compute a running total and a rank within each group without collapsing the rows.
CREATE TABLE sales (day INTEGER, region TEXT, amount INTEGER);
INSERT INTO sales VALUES (1, 'N', 10), (2, 'N', 20), (3, 'N', 5), (1, 'S', 7), (2, 'S', 7), (3, 'S', 30);
SELECT day, region, amount,
SUM(amount) OVER (PARTITION BY region ORDER BY day) AS running_total,
RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
FROM sales
ORDER BY region, day;1|N|10|10|2 2|N|20|30|1 3|N|5|35|3 1|S|7|7|2 2|S|7|14|2 3|S|30|44|1
Explanation
A GROUP BY query collapses each group into one row. A window function computes a value over a set of related rows but keeps every row. The OVER clause defines the window: PARTITION BY splits the rows into groups, and ORDER BY sets the order inside each group.
SUM(amount) OVER (PARTITION BY region ORDER BY day) is a running total per region. RANK() assigns 1 to the largest amount, and rows with equal values share a rank, so both region S days with amount 7 have rank 2. Window functions need SQLite 3.25 or newer and are standard SQL in PostgreSQL, MySQL 8, SQL Server, and Oracle.
Explore the dry run
CREATE TABLE sales (day INTEGER, region TEXT, amount INTEGER);INSERT INTO sales VALUES (1, 'N', 10), (2, 'N', 20), (3, 'N', 5), (1, 'S', 7), (2, 'S', 7), (3, 'S', 30); SELECT day, region, amount, SUM(amount) OVER (PARTITION BY region ORDER BY day) AS running_total, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_regionFROM salesORDER BY region, day;Follow the example
Start to inspect the source, values, and output. These prepared steps explain the published sample; grouped loop steps describe multiple iterations.
View the complete dry-run table
| Step / line | What happens | State after step | Output so far |
|---|---|---|---|
| Three days of sales for each of two regions. | — | — | |
| For each region, add up the amounts from the first day up to the current day: region N gives 10, 30, 35. | — | — | |
| Rank each day within its region by amount, largest first. Equal amounts share a rank. | — | — | |
| Every original row is still present, now with the two extra columns. | — | 1|N|10|10|2 2|N|20|30|1 3|N|5|35|3 1|S|7|7|2 2|S|7|14|2 3|S|30|44|1 |
Cost and common mistakes
Sorting each partition costs O(n log n) overall.
- The ORDER BY inside OVER and the ORDER BY at the end are independent. The final one only sorts the output.
- RANK leaves gaps after ties (1, 2, 2, 4), while DENSE_RANK does not (1, 2, 2, 3). ROW_NUMBER never ties.
- Without PARTITION BY the window is the whole table.
Try it yourself
How would you get each region's total on every row instead of a running total?
Reveal answer and reasoning
Drop the ORDER BY inside OVER: SUM(amount) OVER (PARTITION BY region) is the same total on every row of the region.