Schema and sample data
| sale_id | sale_date | amount |
|---|---|---|
| 1 | 2024-01-01 | 100.00 |
| 2 | 2024-01-05 | 250.00 |
| 3 | 2024-01-10 | 175.00 |
| 4 | 2024-01-15 | 300.00 |
| 5 | 2024-01-20 | 125.00 |
| 6 | 2024-01-25 | 220.00 |
Show setup SQL (copy to run)
CREATE TABLE Sales (
sale_id INT PRIMARY KEY,
sale_date DATE NOT NULL,
amount DECIMAL(10,2) NOT NULL
);
INSERT INTO Sales (sale_id, sale_date, amount) VALUES
(1, '2024-01-01', 100.00),
(2, '2024-01-05', 250.00),
(3, '2024-01-10', 175.00),
(4, '2024-01-15', 300.00),
(5, '2024-01-20', 125.00),
(6, '2024-01-25', 220.00);From the Sales table, return each sale_date and its amount, plus a running_total column that accumulates amount from the earliest date up to and including the current row. Use a window function with an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame. Sort the result by sale_date ascending.
Expected result
| sale_date | amount | running_total |
|---|---|---|
| 2024-01-01 | 100.00 | 100.00 |
| 2024-01-05 | 250.00 | 350.00 |
| 2024-01-10 | 175.00 | 525.00 |
| 2024-01-15 | 300.00 | 825.00 |
| 2024-01-20 | 125.00 | 950.00 |
| 2024-01-25 | 220.00 | 1170.00 |
Show hint
Reach for SUM(amount) OVER (...). Inside the OVER clause, order the rows by sale_date and add a frame clause so the sum covers every row from the start of the ordering through the current one. Spell out the frame with ROWS rather than leaving it to the default.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT
sale_date,
amount,
SUM(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM Sales
ORDER BY sale_date;A running total is a cumulative sum: each row shows the total of every amount from the first date through the current one. The SUM aggregate becomes a window function the moment you add an OVER clause, so instead of collapsing the table into one grand total it produces one value per row. Ordering the window by sale_date defines what "so far" means, and the frame clause defines exactly which rows are summed for each output row.
The frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means "from the very first row of the ordering up to and including this one." Walking the sorted rows: 100, then 100 + 250 = 350, then + 175 = 525, then + 300 = 825, then + 125 = 950, and finally + 220 = 1170. That matches the running_total column exactly.
Specifying ROWS is not optional decoration. When you supply ORDER BY without a frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE is defined in terms of ordering-key values rather than physical rows. With RANGE, all rows that tie on sale_date are folded into the same cumulative value, so if two sales shared a date they would both show the total through that whole date instead of a per-row running sum. The dates here are distinct, so both frames return the same numbers, but writing ROWS makes the intent explicit and keeps the result correct if ties ever appear. ROWS is also cheaper for the optimizer because it does not have to scan for value peers.
To keep a separate running total per group, add PARTITION BY: for example SUM(amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) restarts the accumulation at the first row of each region. The most common mistake is omitting the frame entirely and being surprised by tied dates collapsing together. For more cumulative-window patterns, see the running total window question.