Running Totals and Cumulative Metrics in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
What are Running Totals in SQL?
A running total accumulates a metric across an ordered sequence of rows so that each row carries the sum of all preceding rows plus its own value. Cumulative revenue, cumulative signups, cumulative events — all follow the same two-stage pattern: aggregate by period first, then accumulate across periods with a window function.
Your manager wants cumulative revenue for the year, showing how total revenue grows each day. The raw orders table has one row per order, not one per day. You need to aggregate to day first, then accumulate. Running the window function directly on the unaggregated table gives you a running total per transaction — one row per order, each carrying the sum of all prior orders. That answers a different question.
WITH daily_revenue AS (
SELECT
ordered_at::date AS order_date,
SUM(total_amount) AS revenue
FROM orders
GROUP BY ordered_at::date
)
SELECT
order_date,
revenue,
SUM(revenue) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_revenue
FROM daily_revenue
ORDER BY order_dateWITH daily AS ( SELECT ordered_at::date AS order_date, SUM(total_amount) AS revenue FROM orders GROUP BY ordered_at::date ) SELECT order_date, revenue, SUM(revenue) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue FROM daily ORDER BY order_date
The CTE produces one row per day. The window function in the outer query accumulates revenue from the first day through each current day. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW expands the frame by exactly one row at each step — appropriate here because the data has one row per period after aggregation. If the pre-aggregated result ever had two rows with the same date, RANGE would treat them as a single frame boundary and give both rows the same cumulative total, while ROWS would treat them as separate steps. For pre-aggregated data with one row per period, the two behave identically.
Why do you aggregate before computing a running total?
The two-stage structure is deliberate
Applying the window function directly on the unaggregated table produces a running total at the individual transaction level, not the period level. "Cumulative revenue as of each month" requires aggregating to the month first. Skipping the aggregation step produces a running total that increments on every transaction, which answers a different question.
How do you compute a running total per group?
PARTITION BY for per-entity running totals
To compute cumulative revenue per region (not globally), add PARTITION BY:
SUM(revenue) OVER (
PARTITION BY region
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_revenue_by_regionWithout PARTITION BY, the accumulation crosses region boundaries and produces a single global total.
What do gaps in the data do to a running total?
The one thing that trips people up
Gaps in the data affect running totals. If the pre-aggregated data has missing days (no orders on that day, so no row), the running total jumps from the day before the gap to the day after. There's no row for the gap period to show a repeated cumulative total. When the requirement is a continuous series with every day represented, build a date spine first and zero-fill the missing days before running the accumulation.
Practice Running Totals in SQL
Scenario: Brightlane's operations team is tracking how total order volume has grown over time and needs each day's order count alongside the cumulative count from the start of the data through that day.
Task: Write a query to return each order day, the number of orders placed on that day, and the running total of all orders placed from the earliest day through that day.
Assumptions:
- An order day is identified by its date.
- A day's
daily_ordersis the count ofordersplaced on that day. - A day's
cumulative_ordersis the combined order count from the earliest day in the data through that day inclusive.
Output:
- One row per order day present in the data.
- Columns in this order:
order_day,daily_orders,cumulative_orders. - Sorted by
order_dayascending.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution10 Running Totals practice problems
Write a query to return each order day, the number of orders placed on that day, and the running total of all orders placed from the earliest day through that day.
Write a query to return each calendar month in which users signed up, the count of new users that month, and the running total of all users signed up from the earliest signup month through that month.
Write a query to return each order month, the total orders revenue for that month, and the running total of all orders revenue from the earliest month through that month.
Write a query to return each (order_month, status) combination, the count of orders with that status in that month, and the running total of orders in that status from the earliest such month through the current month — restarting at the earliest month for each status independently.
Write a query to return each signup month, the count of new users that month, and the rolling 3-month total — covering the current month and the two months immediately preceding it.
Write a query to return each order month in which at least one delivered order was placed, the total delivered revenue for that month, and the running total of delivered revenue from the earliest such month through that month.
Write a query to return each calendar month in which at least one salary became effective, the total salary amount that became effective in that month, and the running total of all salary commitments from the earliest such month through that month.
Write a query to return each order month, its calendar order_year, the revenue for that month, and the ytd_revenue — the running total from the start of that calendar year through that month, restarting at the start of each new year.
Write a query to return each order day, the average order value for that day alone, and the running average order value across every individual order placed from the earliest day through that day.
Write a query to return each order month, the revenue for that month, the cumulative revenue from the earliest month through that month, and the remaining revenue from that month through the latest month in the data.
Start learning to practice all 10 Running Totals problems, with instant grading and mastery tracking.
Common questions about Running Totals
Why does my running total jump instead of increasing one row at a time?
Because of ties. With ORDER BY and no frame, a window defaults to a RANGE frame, which treats rows sharing the same ORDER BY value as one step and adds them all at once. Two rows on the same date both show the combined total. Add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to accumulate strictly row by row.
How do I restart a running total for each customer?
Add PARTITION BY customer_id inside the OVER clause. The sum starts again from zero at every partition boundary, so each customer gets their own running total.
Is a running total the same as a cumulative sum?
Yes. They are two names for one thing: each row carries the total of its own value plus every row before it in the chosen order.
Can I calculate a running total without a window function?
Yes, with a correlated subquery that sums every earlier row, but it has to reconsider the earlier rows once for each row in the result. A window function computes the whole column in a single ordered pass, which is why it is the standard answer.
