SQL · Guides · October 29, 2026 · 13 min read

Running total in SQL: cumulative sum, by group, resets and frames

A running total (also called a cumulative sum) adds each row’s value to everything before it: day 1, then day 1 + day 2, then day 1 + day 2 + day 3, and so on. In modern SQL you write it with a window function: SUM(value) OVER (ORDER BY day). Add PARTITION BY and the total starts again for every group; add a frame clause such as ROWS BETWEEN 6 PRECEDING AND CURRENT ROW and it becomes a moving sum instead.

That one line hides several decisions: what happens with ties in the sort column, whether a filter runs before or after the window, and how a frame behaves when some days are missing. This guide walks through each of them on a real dataset: a training airline database with bookings, tickets and flights from summer 2026 (money in US dollars). Every query below was run on it, and every table shows the actual output, so you can paste any example into the Nomto SQL room and get the same numbers.

The basic running total with SUM() OVER

Start with daily revenue from the bookings table, then add a cumulative column:

SELECT
  CAST(book_date AS DATE) AS day,
  COUNT(*) AS bookings,
  SUM(total_amount) AS revenue,
  SUM(SUM(total_amount)) OVER (ORDER BY CAST(book_date AS DATE)) AS running_revenue
FROM bookings
WHERE book_date >= '2026-08-01'
GROUP BY CAST(book_date AS DATE)
ORDER BY day
LIMIT 7;

Two things are worth noticing.

SUM(SUM(...)) OVER is not a typo. The inner SUM is the ordinary aggregate that GROUP BY computes for each day. The outer SUM ... OVER is a window function that runs after grouping and adds up those daily values. Window functions are evaluated after WHERE, GROUP BY and HAVING, so they can see aggregated results.

The running total starts at 1 August, not at the first booking. The WHERE clause removed everything earlier before the window was computed. Sometimes that is what you want (“revenue since the start of the month”); often it is not. The next section shows the difference.

Most people find the version with a CTE easier to read: first build the daily table, then add the window on top.

WITH daily AS (
  SELECT CAST(book_date AS DATE) AS day, SUM(total_amount) AS revenue
  FROM bookings
  GROUP BY CAST(book_date AS DATE)
)
SELECT day, revenue,
  SUM(revenue) OVER (ORDER BY day) AS running_revenue
FROM daily
ORDER BY day;

Filter after the window, not before

Suppose you want month-to-date revenue for the last days of July and the first days of August. A tempting query puts the date filter in the same SELECT as the window. The WHERE runs first, so July’s running total would start at 29 July and lose everything from 1 to 28 July.

The fix is to compute the window in one step and filter in the next:

WITH daily AS (
  SELECT CAST(book_date AS DATE) AS day, SUM(total_amount) AS revenue
  FROM bookings
  GROUP BY CAST(book_date AS DATE)
),
mtd AS (
  SELECT day, revenue,
    SUM(revenue) OVER (PARTITION BY date_trunc('month', day) ORDER BY day) AS mtd_revenue
  FROM daily
)
SELECT *
FROM mtd
WHERE day BETWEEN '2026-07-29' AND '2026-08-03'
ORDER BY day;

Now 29 July carries the whole July total up to that day, $127.2 million, and the counter drops back to one day’s revenue on 1 August. If the filter had been in the same query as the window, the 29 July row would have shown only 4,423,319.

Rule of thumb: conditions that define the population go in WHERE before the window; conditions that only choose which rows to display go in an outer query after it.

Running total that resets: PARTITION BY

The query above already used the reset mechanism. PARTITION BY splits the rows into independent groups, and the running total starts from zero in each one. Partition by a calendar period and you get month-to-date or year-to-date figures; partition by a category and you get a separate cumulative line per category.

Here is revenue from flight segments by departure day, with a separate running total for each cabin class:

WITH daily AS (
  SELECT CAST(f.scheduled_departure AS DATE) AS day,
    tf.fare_conditions,
    SUM(tf.amount) AS revenue
  FROM ticket_flights tf
  JOIN flights f ON f.flight_id = tf.flight_id
  GROUP BY CAST(f.scheduled_departure AS DATE), tf.fare_conditions
)
SELECT day, fare_conditions, revenue,
  SUM(revenue) OVER (PARTITION BY fare_conditions ORDER BY day) AS running_revenue
FROM daily
WHERE day <= '2026-07-14'
ORDER BY fare_conditions, day;

(Eight of nine rows shown; Economy on 14 July reaches 4267240.00.)

The WHERE day <= '2026-07-14' here is safe: flights start on 12 July, so cutting off the end of the period does not change the totals of the rows that remain. A filter on the end of a range never affects a running total that looks backwards; a filter on the start does.

You can partition by several columns at once, for example PARTITION BY fare_conditions, date_trunc('month', day) for a month-to-date figure per class.

Ties: why ROWS and RANGE give different answers

When you write ORDER BY inside OVER and no frame, SQL uses the default frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. “RANGE” means “every row with a sort value up to and including the current one”, and that includes all rows that tie with the current row. With unique sort values you never notice. With duplicates, tied rows get the same running total.

Bookings are stored to the minute, and sometimes two land on the same minute:

WITH t AS (
  SELECT book_ref, book_date, total_amount,
    SUM(total_amount) OVER (ORDER BY book_date) AS range_total,
    SUM(total_amount) OVER (ORDER BY book_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total,
    SUM(total_amount) OVER (ORDER BY book_date, book_ref) AS tiebroken
  FROM bookings
)
SELECT *
FROM t
WHERE book_date BETWEEN '2026-06-20 01:58' AND '2026-06-20 02:21'
ORDER BY book_date, book_ref;

The two bookings at 02:01 both show 35690.00 under the default RANGE frame: each one “sees” the other. With ROWS, the first of the pair shows 35430.00 and the second 35690.00, a proper step-by-step total.

But ROWS alone has its own problem: the order of tied rows is not defined, so which booking comes first can change between runs or databases. The robust fix is the last column: add a unique tie-breaker to the ORDER BY (book_date, book_ref). Then every row has its own position, RANGE and ROWS agree, and the result is repeatable.

Which is correct depends on the question:

• “Total revenue as of 02:01” — the RANGE answer is right; both bookings had happened by then.
• “Running balance after each transaction” — you want one step per row, so use a unique order (and ROWS if you like being explicit).

Moving sums: ROWS BETWEEN n PRECEDING

Change the start of the frame and the running total becomes a moving (rolling) total. A 7-day rolling revenue:

WITH daily AS (
  SELECT CAST(book_date AS DATE) AS day, SUM(total_amount) AS revenue
  FROM bookings
  GROUP BY CAST(book_date AS DATE)
)
SELECT day, revenue,
  SUM(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d,
  COUNT(*) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS days_in_window
FROM daily
ORDER BY day
LIMIT 8;

The days_in_window column shows a trap at the start of a series: the first six rows have fewer than seven days in their window, so their “7-day” sums are not comparable with later ones. Either hide them (WHERE days_in_window = 7 in an outer query) or label them clearly. For a rolling average, AVG over the same frame divides by the number of rows actually in the window, which hides the problem even better, so always check the count.

When days are missing: RANGE with an interval

ROWS BETWEEN 6 PRECEDING means “the previous six rows”, not “the previous six days”. If the series has gaps, six rows can cover two weeks. Flights from Moscow Domodedovo (DME) to Petropavlovsk-Kamchatsky (PKC) do not run every day, which makes the difference obvious:

WITH daily AS (
  SELECT CAST(f.scheduled_departure AS DATE) AS day, COUNT(*) AS passengers
  FROM flights f
  JOIN ticket_flights tf ON tf.flight_id = f.flight_id
  WHERE f.departure_airport = 'DME' AND f.arrival_airport = 'PKC'
  GROUP BY CAST(f.scheduled_departure AS DATE)
)
SELECT day, passengers,
  SUM(passengers) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rows_7,
  SUM(passengers) OVER (ORDER BY day
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) AS range_7d
FROM daily
ORDER BY day
LIMIT 8;

By 30 July the ROWS version adds up seven departures spread over 15 days, 16 to 30 July (470 passengers), while the RANGE version correctly covers 24–30 July only (213). RANGE with an interval offset works in PostgreSQL 11 and later when the sort column is a date or timestamp.

The other cure is to fill the missing days with zeros before applying the window, using a calendar built with generate_series. That approach is covered step by step in our guide on filling missing dates with a date spine.

A quick summary of frames

Practical patterns built on a running total

When did the total cross a threshold?

Compute the running total, then take the first row where it reaches the target. Here: the day cumulative booking revenue first passed $100 million.

WITH daily AS (
  SELECT CAST(book_date AS DATE) AS day, SUM(total_amount) AS revenue
  FROM bookings
  GROUP BY CAST(book_date AS DATE)
),
cum AS (
  SELECT day, SUM(revenue) OVER (ORDER BY day) AS running_revenue
  FROM daily
)
SELECT day, running_revenue
FROM cum
WHERE running_revenue >= 100000000
ORDER BY day
LIMIT 1;

You cannot put the window function itself in WHERE; that is why the running total lives in a CTE first.

Cumulative share (Pareto analysis)

Divide a running total by the grand total and you get a cumulative share. Sort routes by revenue, and you can see how concentrated the business is:

WITH route AS (
  SELECT f.departure_airport || '-' || f.arrival_airport AS route,
    SUM(tf.amount) AS revenue
  FROM ticket_flights tf
  JOIN flights f ON f.flight_id = tf.flight_id
  GROUP BY f.departure_airport || '-' || f.arrival_airport
),
ranked AS (
  SELECT route, revenue,
    SUM(revenue) OVER (ORDER BY revenue DESC, route
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_revenue,
    SUM(revenue) OVER () AS total,
    ROW_NUMBER() OVER (ORDER BY revenue DESC, route) AS rn,
    COUNT(*) OVER () AS routes
  FROM route
)
SELECT rn, route, revenue,
  ROUND(100.0 * cum_revenue / total, 1) AS cum_share_pct,
  routes
FROM ranked
WHERE rn <= 5
ORDER BY rn;

Two windows sit side by side: SUM(revenue) OVER () with an empty OVER is the grand total on every row, and the ordered one is the running total. Counting how many rows have cum_share up to 0.5 shows that 39 of the 457 routes bring in half of the segment revenue. Note 100.0 rather than 100: in PostgreSQL, integer divided by integer drops the fraction.

Older alternatives and performance

Before window functions, running totals were written with a correlated subquery or a self-join: for each row, sum all rows with an earlier date. It gives the same result but reads the table once per row, so it slows down quadratically as the table grows. A window function sorts once and walks the rows in a single pass. In PostgreSQL an index on the PARTITION BY and ORDER BY columns can save the sort; on aggregated daily tables the cost is negligible either way.

If you need the running total in several places, compute it once in a CTE or a view instead of repeating the window.

Common mistakes checklist

• Filtering in the same query as the window when the filter should not change the population. Compute first, filter in an outer query.
• No tie-breaker in ORDER BY. Add a unique column if each row should be its own step.
• ROWS on a series with missing days. Use RANGE with an interval or fill the calendar first.
• Forgetting PARTITION BY and getting one line across all groups when you wanted a reset per group.
• Integer division in shares: multiply by 100.0 first.
• Comparing early rolling values that have fewer rows in their window than later ones.

Every query in this guide uses tables from the Nomto SQL course, so you can run each one in the SQL room, change the partition or the frame, and watch how the numbers move.

FAQ

How do I calculate a running total in SQL?

Use a window function: SUM(amount) OVER (ORDER BY date_column). If the data has several rows per date, aggregate first (with GROUP BY or in a CTE) and apply the window to the daily totals.

How do I make a running total reset for each group or month?

Add PARTITION BY inside OVER: SUM(revenue) OVER (PARTITION BY customer_id ORDER BY day) for a per-customer total, or PARTITION BY date_trunc('month', day) for month-to-date.

What is the difference between ROWS and RANGE in a running total?

ROWS counts physical rows; RANGE works with values of the sort column, so rows with equal values (ties) are treated as one step and get the same total. With a unique sort key both give the same result. RANGE also accepts interval offsets like INTERVAL '6 days' PRECEDING.

Can I use a running total in a WHERE clause?

Not directly, because window functions are computed after WHERE. Put the running total in a CTE or subquery and filter on it in the outer query.

Does this syntax work in MySQL and SQL Server?

SUM() OVER (PARTITION BY ... ORDER BY ...) works in PostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite 3.25+ and most analytical databases. Support for RANGE with interval offsets varies; PostgreSQL has it from version 11.

Also in 简体中文