Running total by month
A cumulative total that grows month by month, with a window SUM.
WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month,
SUM(amount) AS amount
FROM orders
GROUP BY strftime('%Y-%m', order_date)
)
SELECT month,
amount,
SUM(amount) OVER (ORDER BY month) AS running_total
FROM monthly
ORDER BY month;How it works
The CTE adds up the orders per month. SUM(amount) OVER (ORDER BY month) then adds each month to all the months before it.
strftime('%Y-%m', ...) is the SQLite way to get the year and month. Other databases use another function: DATE_TRUNC('month', order_date) in PostgreSQL, DATE_FORMAT(order_date, '%Y-%m') in MySQL, FORMAT(order_date, 'yyyy-MM') in SQL Server.
What your tables need
- customers(customer_id, customer_name, region) - orders(order_id, customer_id, order_date, amount, status)
Rename the tables and columns to match your own database. Dates are stored as text in the form 2024-03-31 (or as a real date type, which works the same way here).
Small example
Input data
orders
order_id | customer_id | order_date | amount | status
1 | 1 | 2024-01-05 | 100 | paid
2 | 1 | 2024-02-10 | 150 | paid
3 | 2 | 2024-01-20 | 200 | paid
4 | 2 | 2024-03-02 | 50 | refunded
5 | 3 | 2024-02-14 | 300 | paid
6 | 1 | 2024-03-15 | 250 | paid
7 | 3 | 2024-03-20 | 120 | paid
8 | 2 | 2024-03-22 | 200 | paidExpected result
month | amount | running_total
2024-01 | 300.0 | 300.0
2024-02 | 450.0 | 750.0
2024-03 | 620.0 | 1370.0Common mistakes and limitations
- Leaving out the CTE and writing SUM(SUM(amount)) OVER (...) in one query. It works in some databases, but it is harder to read. - A month with no orders does not appear at all, so the running total skips it. Join to a calendar table if you need every month. - Without ORDER BY inside OVER, SUM gives the same grand total on every row.