SQL: window functions for running totals and ranks

Before you start

SQL GROUP BY and ORDER BY.

Compute a running total and a rank within each group without collapsing the rows.
Predict the sample output and explain the state changes.
Complete the practice task and check the boundary cases.

The concept

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.

Code example

SQL

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;

Output

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

Example details

Follow the code

Open the walkthrough to inspect the example one step at a time.

Show guided walkthrough

Guided walkthroughReady

1CREATE TABLE sales (day INTEGER, region TEXT, amount INTEGER);

2INSERT INTO sales VALUES (1, 'N', 10), (2, 'N', 20), (3, 'N', 5), (1, 'S', 7), (2, 'S', 7), (3, 'S', 30);

4SELECT day, region, amount,

5 SUM(amount) OVER (PARTITION BY region ORDER BY day) AS running_total,

6 RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region

7FROM sales

8ORDER 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

Prepared trace of the published program. Some rows group repeated iterations.
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

Behind the scenes

How SQL processes this code

The database parses and plans the query, accesses the table, and returns the selected rows. Physical execution order may differ from the query’s written order.

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.