Running Total in SQL (Window Functions): Cumulative Sales by Date
SUM with OVER adds up sales row by row, so each order shows the total so far. Build a running total by date, fix same-day ties with a tie breaker, and restart the total every month for month-to-date sales.
Step by step
Running total
- Sales so far. A running total shows how much has been sold so far, row by row. In SQL you build it with SUM and a window, written as OVER.
December orders
- The orders. Start with a simple query on the orders table. Select order_id, order_date and order_total, and keep only orders from 1 December 2025 onwards with WHERE order_date >= '2025-12-01'. Run it. You get 12 December orders, each with its ID, date and total.
SELECT order_id, order_date, order_total FROM orders WHERE order_date >= '2025-12-01';
SUM OVER
- SUM OVER. After order_total, add SUM(order_total) OVER (ORDER BY order_date) AS running_total. OVER turns the sum into a window: each row adds itself and every row before it, in date order. Run it again. There are still 12 rows, now with a running_total column that climbs: 118, then 250, then 322, and so on.
order_total, SUM(order_total) OVER (ORDER BY order_date) AS running_total
Same-day ties
- Break the ties. Look at 20 December. Both orders show the same running total, because rows with the same date are added together. Change the ORDER BY inside OVER to order_date, order_id so order_id breaks the tie. Run it. Now each row climbs on its own, one order at a time, even on days with more than one order.
order_date, order_id) AS running_total
The whole year
- The whole year. The WHERE line decides which orders go into the total. Delete it so the query ends at FROM orders;. Run it. You now get 141 rows, and the running total starts with the first order in January and keeps growing all year.
;
Month to date
- Restart each month. For month-to-date sales, add PARTITION BY strftime('%Y-%m', order_date) inside OVER, before the ORDER BY. strftime keeps just the year and month, so each month is its own group. Name the column mtd. Run it. Each order now shows sales so far in its own month, and the total starts again from zero at each new month. The last order of each month holds that month's total.
SELECT order_id, order_date, order_total, SUM(order_total) OVER ( PARTITION BY strftime('%Y-%m', order_date) ORDER BY order_date, order_id) AS mtd FROM orders;
Recap
- Running totals. In short: SUM(...) OVER (ORDER BY date) gives a running total. Add a unique column such as order_id as a tie breaker for rows on the same day. Add PARTITION BY month to restart it every month.
Common mistakes
- Orders on the same day share one total. With only ORDER BY order_date, rows with the same date are added together and show the same number, like the two orders on 20 December. Add a unique column after the date, such as ORDER BY order_date, order_id.
- Leaving ORDER BY out of OVER. SUM(order_total) OVER () gives the grand total on every row, not a running total. The ORDER BY inside OVER is what makes it add up row by row.
- Expecting OVER to sort the results. The ORDER BY inside OVER sets how the total adds up, not how the rows are shown. Add ORDER BY order_date, order_id at the end of the query so the list reads in the same order as the total climbs.
- Using strftime outside SQLite. strftime is a SQLite function. In SQL Server, use PARTITION BY YEAR(order_date), MONTH(order_date). In Oracle, use PARTITION BY TO_CHAR(order_date, 'YYYY-MM').
When a business analyst uses this
During UAT of a sales dashboard, run the month-to-date query and compare the last order of each month with the dashboard's monthly total. Any month that doesn't match goes in the defect log before sign-off.
Questions people ask
What is a running total in SQL?
A running total, also called a cumulative sum, shows on each row the total of that row plus every row before it. In SQL you get it with SUM(column) OVER (ORDER BY column).
How do I get a running total in SQL Server?
Use the same window function: SUM(order_total) OVER (ORDER BY order_date, order_id). It works in SQL Server 2012 and later. To restart each month, use PARTITION BY YEAR(order_date), MONTH(order_date).
How do I get a running total in Oracle SQL?
Oracle supports the same SUM(order_total) OVER (ORDER BY order_date, order_id) syntax. To restart each month, use PARTITION BY TO_CHAR(order_date, 'YYYY-MM') in place of strftime.
