Running Totals
Adding up as you go.
Helen has a target. One thousand orders in twenty twenty five. At the end of each month, she wants to know, how many orders so far this year? That's a running total. Each month adds its orders to everything before it.

- Target: 1,000 orders in 2025
- How many so far?
- A running total
SUM OVER ORDER BY
The trick is sum, with over, and order by month. When a window has an order by, the sum only adds rows up to the current one. So each month's total includes every month before it. Run it. Helen passed one thousand orders in December, finishing on one thousand and thirty nine.
WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY month
)
SELECT month,
orders,
SUM(orders) OVER (ORDER BY month) AS running_total
FROM monthly;โโโโโโโโโโโฌโโโโโโโโโฌโโโโโโโโโโโโโโโโ โ month โ orders โ running_total โ โโโโโโโโโโโผโโโโโโโโโผโโโโโโโโโโโโโโโโค โ 2025-01 โ 69 โ 69 โ โ 2025-02 โ 75 โ 144 โ โ 2025-03 โ 61 โ 205 โ โ 2025-04 โ 73 โ 278 โ โ 2025-05 โ 103 โ 381 โ โ 2025-06 โ 88 โ 469 โ โ 2025-07 โ 77 โ 546 โ โ 2025-08 โ 120 โ 666 โ โ 2025-09 โ 93 โ 759 โ โ 2025-10 โ 93 โ 852 โ โ 2025-11 โ 83 โ 935 โ โ 2025-12 โ 104 โ 1039 โ โโโโโโโโโโโดโโโโโโโโโดโโโโโโโโโโโโโโโโ
Restart each year
Add partition by year, and the running total starts again at zero every January. That's called year to date. Notice we filter to the last three months in a second step. If we filtered first, the window would only see those three months. Run it. By December twenty twenty four, Helen had five hundred and fifty nine orders for the year. By December twenty twenty five, one thousand and thirty nine.
WITH monthly AS (
SELECT strftime('%Y', order_date) AS year,
strftime('%m', order_date) AS month,
COUNT(*) AS orders
FROM orders
GROUP BY year, month
),
ytd AS (
SELECT year, month, orders,
SUM(orders) OVER (PARTITION BY year ORDER BY month) AS year_to_date
FROM monthly
)
SELECT * FROM ytd
WHERE month IN ('10', '11', '12');โโโโโโโโฌโโโโโโโโฌโโโโโโโโโฌโโโโโโโโโโโโโโโ โ year โ month โ orders โ year_to_date โ โโโโโโโโผโโโโโโโโผโโโโโโโโโผโโโโโโโโโโโโโโโค โ 2024 โ 10 โ 59 โ 440 โ โ 2024 โ 11 โ 54 โ 494 โ โ 2024 โ 12 โ 65 โ 559 โ โ 2025 โ 10 โ 93 โ 852 โ โ 2025 โ 11 โ 83 โ 935 โ โ 2025 โ 12 โ 104 โ 1039 โ โโโโโโโโดโโโโโโโโดโโโโโโโโโดโโโโโโโโโโโโโโโ
Summary
Sum, over, order by makes a running total. Add partition by to restart it for each group. Next, comparing each row with the one before.
Key takeaways- SUM() OVER (ORDER BY ...)
- Adds up as it goes
- PARTITION BY restarts it