SQL practice › SQL reference › Running total
SQL running total: SUM() OVER (ORDER BY ...), with queries you can run
A running total is SUM used as a window function with an ORDER BY inside OVER: SUM(amount) OVER (ORDER BY order_date). Each row gets the sum of its own amount and of every row before it. Add PARTITION BY customer_id and the total starts again for each customer.
The catch: two rows with the same order_date get the same running total, because by default the sum runs to the last row that ties with the current one. Make the ORDER BY unique (ORDER BY order_date, id) and add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to get a total that grows row by row.
Syntax
SELECT id, order_date, amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM ordersSUM(amount) OVER (SUMas a window function: every row is kept and gets a sum beside it.PARTITION BY customer_id- Optional. The total starts again at each customer. Leave it out for one running total over all rows.
ORDER BY order_date, id- The order in which rows are added.
idis a tie-breaker, so two orders on the same date still have a first and a second. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW- The frame: from the first row of the partition to this row. When the
ORDER BYhas no ties you can leave it out, because the default frame then covers the same rows.
The data
Nine orders of a small furniture shop, in date order. Look at the last two: orders 108 and 109 were placed on the same day. Every other date appears once.
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | 4 | Chair | 120 | 2026-03-14 |
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | 4 | Chair | 120 | 2026-03-14 |
| id | order_date | amount | running_total |
|---|---|---|---|
| 101 | 2026-01-05 | 240 | 240 |
| 102 | 2026-01-09 | 35 | 275 |
| 103 | 2026-01-17 | 120 | 395 |
| 104 | 2026-02-02 | 180 | 575 |
| 105 | 2026-02-11 | 240 | 815 |
| 106 | 2026-02-20 | 35 | 850 |
| 107 | 2026-03-03 | 35 | 885 |
| 108 | 2026-03-14 | 120 | 1125 |
| 109 | 2026-03-14 | 120 | 1125 |
Why rows with the same date get the same total
A window function with an ORDER BY and nothing else uses the default frame, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. In RANGE mode, "current row" means the current row and its peers: the rows that have the same value in the ORDER BY. So for order 108 the sum runs through every order dated 2026-03-14, and that includes order 109.
Sometimes that is what you want: the total at the end of each day. When you want one row at a time, two things are needed. ROWS instead of RANGE makes "current row" mean this row only. And a second column in the ORDER BY decides which of the tied rows comes first.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | 4 | Chair | 120 | 2026-03-14 |
| id | order_date | amount | running_total |
|---|---|---|---|
| 101 | 2026-01-05 | 240 | 240 |
| 102 | 2026-01-09 | 35 | 275 |
| 103 | 2026-01-17 | 120 | 395 |
| 104 | 2026-02-02 | 180 | 575 |
| 105 | 2026-02-11 | 240 | 815 |
| 106 | 2026-02-20 | 35 | 850 |
| 107 | 2026-03-03 | 35 | 885 |
| 108 | 2026-03-14 | 120 | 1005 |
| 109 | 2026-03-14 | 120 | 1125 |
, id in the ORDER BY no two rows tie any more, so you can delete the ROWS line and get the same numbers. The other way round is not safe: ROWS with only ORDER BY order_date runs, but which of the two same-day orders is added first is then up to the database.Running total by group: PARTITION BY
For a separate total per customer, per account or per product, add PARTITION BY. The sum starts again at the first row of each group.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | 4 | Chair | 120 | 2026-03-14 |
| customer_id | order_date | amount | spent_so_far |
|---|---|---|---|
| 1 | 2026-01-05 | 240 | 240 |
| 1 | 2026-01-17 | 120 | 360 |
| 1 | 2026-03-03 | 35 | 395 |
| 2 | 2026-01-09 | 35 | 35 |
| 2 | 2026-02-11 | 240 | 275 |
| 3 | 2026-02-02 | 180 | 180 |
| 3 | 2026-03-14 | 120 | 300 |
| 4 | 2026-02-20 | 35 | 35 |
| 4 | 2026-03-14 | 120 | 155 |
PARTITION BY customer_id and it is one total for the whole shop again, ending at 1125.Running total by date or by month
A running total with one row per day or per month takes two steps: first fold the orders into one row per period with GROUP BY, then run the total over those rows. A CTE keeps the steps apart.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | 4 | Chair | 120 | 2026-03-14 |
| month | month_total | running_total |
|---|---|---|
| 2026-01 | 395 | 395 |
| 2026-02 | 455 | 850 |
| 2026-03 | 275 | 1125 |
strftime('%Y-%m', ...) is how SQLite cuts a date down to its month. It also fits in one query, because window functions are computed after GROUP BY: SUM(SUM(amount)) OVER (ORDER BY ...), where the inner SUM is the month's total and the outer one adds the months up.A moving average: the same idea with a shorter frame
A running total's frame starts at the first row. Start it a fixed number of rows back and you get a moving window: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is this row and the two before it.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | 4 | Chair | 120 | 2026-03-14 |
| id | order_date | amount | avg_last_3 |
|---|---|---|---|
| 101 | 2026-01-05 | 240 | 240 |
| 102 | 2026-01-09 | 35 | 137.5 |
| 103 | 2026-01-17 | 120 | 131.7 |
| 104 | 2026-02-02 | 180 | 111.7 |
| 105 | 2026-02-11 | 240 | 180 |
| 106 | 2026-02-20 | 35 | 151.7 |
| 107 | 2026-03-03 | 35 | 103.3 |
| 108 | 2026-03-14 | 120 | 63.3 |
| 109 | 2026-03-14 | 120 | 91.7 |
AVG to SUM for a moving total.Practice: 4 exercises
Write the query in the box and press Run. Your result is compared with the right one, on this table and again on changed data, so a query with the values typed in won't pass. Column names don't matter; the columns and rows do.
Return each order's id, its amount and the running total of amount. Add the orders in order of order_date, and by id when two orders share a date. Every order adds only its own amount: order 108 should show 1005 and order 109 should show 1125.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | 4 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT id, amount,
SUM(amount) OVER (
ORDER BY order_date, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
FROM orders| id | amount | SUM(amount) OVER ( ORDER BY order_date, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) |
|---|---|---|
| 101 | 240 | 240 |
| 102 | 35 | 275 |
| 103 | 120 | 395 |
| 104 | 180 | 575 |
| 105 | 240 | 815 |
| 106 | 35 | 850 |
| 107 | 35 | 885 |
| 108 | 120 | 1005 |
| 109 | 120 | 1125 |
ORDER BY order_date, id gives every order its own place, and ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW sums from the first order to the current one. With that ORDER BY there are no ties, so SUM(amount) OVER (ORDER BY order_date, id) on its own returns the same numbers.
For each order return its customer_id, its id and the running total of what that customer has spent up to and including that order, in order of order_date. No customer has two orders on the same date.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | 4 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT customer_id, id,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date)
FROM orders| customer_id | id | SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) |
|---|---|---|
| 1 | 101 | 240 |
| 1 | 103 | 360 |
| 1 | 107 | 395 |
| 2 | 102 | 35 |
| 2 | 105 | 275 |
| 3 | 104 | 180 |
| 3 | 108 | 300 |
| 4 | 106 | 35 |
| 4 | 109 | 155 |
PARTITION BY customer_id gives each customer their own running total and ORDER BY order_date adds their orders from the oldest to the newest. Customer 1 goes 240, 360, 395. Without the ORDER BY, every row would show the customer's final total.
Return one row per order_date: the date, the total amount ordered that day and the running total up to and including that day.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | 4 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT order_date, SUM(amount),
SUM(SUM(amount)) OVER (ORDER BY order_date)
FROM orders
GROUP BY order_date| order_date | SUM(amount) | SUM(SUM(amount)) OVER (ORDER BY order_date) |
|---|---|---|
| 2026-01-05 | 240 | 240 |
| 2026-01-09 | 35 | 275 |
| 2026-01-17 | 120 | 395 |
| 2026-02-02 | 180 | 575 |
| 2026-02-11 | 240 | 815 |
| 2026-02-20 | 35 | 850 |
| 2026-03-03 | 35 | 885 |
| 2026-03-14 | 240 | 1125 |
GROUP BY order_date folds the two orders of 2026-03-14 into one row with a day total of 240. Window functions are computed after grouping, so the outer SUM(...) OVER (ORDER BY order_date) runs over the eight daily rows and adds up their totals. Computing the daily totals in a CTE first, then SUM(day_total) OVER (ORDER BY order_date), is the same thing in two steps.
Return each order's id, its amount and the average amount of that order and the two orders before it, taking the orders by order_date, then id. The first order averages only itself and the second averages two orders. Don't round the average.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | 4 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT id, amount,
AVG(amount) OVER (
ORDER BY order_date, id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
FROM orders| id | amount | AVG(amount) OVER ( ORDER BY order_date, id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) |
|---|---|---|
| 101 | 240 | 240 |
| 102 | 35 | 137.5 |
| 103 | 120 | 131.67 |
| 104 | 180 | 111.67 |
| 105 | 240 | 180 |
| 106 | 35 | 151.67 |
| 107 | 35 | 103.33 |
| 108 | 120 | 63.33 |
| 109 | 120 | 91.67 |
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW limits the average to three rows: the current one and the two before it in the window's order. At the start of the table the frame is shorter, so the first order averages one row and the second two.
Questions people ask
What does ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW mean?
It is the frame of the window: the rows the sum covers for each row. UNBOUNDED PRECEDING is the first row of the partition and CURRENT ROW is the row being computed, so the sum runs from the start to here. That is the definition of a running total.
Do I need ROWS BETWEEN for a running total?
Not when the ORDER BY inside OVER is unique. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which covers the same rows as long as no two rows tie. It differs when rows share the ORDER BY value: with RANGE they all get the total of the whole tie, with ROWS each gets its own.
What is the difference between ROWS and RANGE?
ROWS counts rows: "current row" is this row. RANGE looks at values: "current row" is this row and every row with the same ORDER BY value. With ROWS and an ORDER BY that can tie, the order among the tied rows is not defined, so add a tie-breaker.
Why does SUM() OVER () give the grand total and SUM() OVER (ORDER BY ...) a running total?
Because of the default frame. Without an ORDER BY, all rows of the partition count as tied with each other, so the frame is the whole partition and every row shows the same total. With an ORDER BY, the frame stops at the current row and its ties, and the sum grows as you go down.
How do I write a running total without a window function?
With a correlated subquery that sums the rows up to the current one: SELECT o.id, (SELECT SUM(x.amount) FROM orders x WHERE x.order_date <= o.order_date) FROM orders o. On this page's table it returns the same numbers as the first live query, including 1125 for both same-day orders. The window function says the same thing in one line and takes PARTITION BY for groups.