Running total in SQL: SUM OVER, ties, groups and monthly resets

A running total in SQL, also called a cumulative sum, comes from the window function SUM(amount) OVER (ORDER BY sale_date). Each row gets the sum of its own amount and every amount before it in that order, and the rows themselves stay in the result. Add PARTITION BY to restart the total for each customer or month, and add a unique column plus ROWS to the ORDER BY when two rows can share a date.

How do I calculate a running total in SQL?

SUM() OVER (ORDER BY ...) calculates a running total in SQL. The ORDER BY inside OVER decides which rows count as “before” the current one, so the total grows in date order. Unlike GROUP BY, a window function keeps every row:

WITH sales(sale_date, amount) AS (
  VALUES (DATE '2026-03-01', 120), (DATE '2026-03-02', 80),
         (DATE '2026-03-03', 200), (DATE '2026-03-04', 50)
)
SELECT sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
ORDER BY sale_date;

Use SUM(amount) OVER (ORDER BY sale_date) for a running total. The same syntax works in PostgreSQL, MySQL 8.0 and SQL Server 2012 and later. The ORDER BY at the end of the query only sorts the output; the one inside OVER is the one that shapes the total.

Why does my running total repeat the same value on the same date?

A running total repeats a value when two rows share the ORDER BY value. With an ORDER BY and no frame, the window frame defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE treats rows with the same date as one group. Both orders on 2 March get the total for the whole day:

WITH orders(id, order_date, amount) AS (
  VALUES (1, DATE '2026-03-01', 120), (2, DATE '2026-03-02', 80),
         (3, DATE '2026-03-02', 30), (4, DATE '2026-03-03', 200)
)
SELECT id, order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY id;

The total is right at the end of each day, which is why the bug survives tests on data with one row per date.

How do I fix a running total with duplicate dates?

Add a unique column, such as the order id, after the date in the window’s ORDER BY, and write the frame as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The id fixes the order of rows on the same day, and ROWS counts one row at a time:

WITH orders(id, order_date, amount) AS (
  VALUES (1, DATE '2026-03-01', 120), (2, DATE '2026-03-02', 80),
         (3, DATE '2026-03-02', 30), (4, DATE '2026-03-03', 200)
)
SELECT id, order_date, amount,
       SUM(amount) OVER (
         ORDER BY order_date, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
ORDER BY id;

Order the window by a unique key and use ROWS for a row-by-row running total. ROWS without the id would also give each row its own total, but which of the two orders comes first would be up to the database, and it could change between runs. The same rule about tied rows decides the RANK vs DENSE_RANK vs ROW_NUMBER results.

How do I calculate a running total by group?

PARTITION BY restarts a running total for each group. SUM(amount) OVER (PARTITION BY customer ORDER BY order_date, id) keeps a separate total for every customer, so Ava’s orders never add to Ben’s:

WITH orders(id, customer, order_date, amount) AS (
  VALUES (1, 'Ava', DATE '2026-03-01', 120), (2, 'Ben', DATE '2026-03-01', 60),
         (3, 'Ava', DATE '2026-03-04', 40), (4, 'Ben', DATE '2026-03-05', 90),
         (5, 'Ava', DATE '2026-03-07', 75)
)
SELECT customer, order_date, amount,
       SUM(amount) OVER (
         PARTITION BY customer
         ORDER BY order_date, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
ORDER BY customer, order_date;

PARTITION BY comes first inside OVER, then ORDER BY, then the frame. Leaving out PARTITION BY gives one running total across all customers, the same mistake as a ranking query that returns only one department.

How do I reset a running total every month?

Partition by the month. PARTITION BY date_trunc('month', sale_date) starts a new running total on the first sale of each month, which gives a month-to-date total on every row:

WITH sales(id, sale_date, amount) AS (
  VALUES (1, DATE '2026-02-26', 100), (2, DATE '2026-02-27', 50),
         (3, DATE '2026-03-01', 70), (4, DATE '2026-03-02', 30),
         (5, DATE '2026-03-09', 25)
)
SELECT sale_date, amount,
       SUM(amount) OVER (
         PARTITION BY date_trunc('month', sale_date)
         ORDER BY sale_date, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS month_to_date
FROM sales
ORDER BY sale_date;

date_trunc is the PostgreSQL spelling. In MySQL 8.0, partition by YEAR(sale_date), MONTH(sale_date) instead, and keep the year: partitioning by the month alone adds March 2025 and March 2026 together.

How do I get a running total of daily totals?

Group by the day first, then run the window over the grouped rows. The window function can wrap the aggregate, as SUM(SUM(amount)) OVER (ORDER BY sale_date): the inner SUM totals each day, and the outer one adds the days up:

WITH orders(id, sale_date, amount) AS (
  VALUES (1, DATE '2026-03-01', 120), (2, DATE '2026-03-01', 30),
         (3, DATE '2026-03-02', 80), (4, DATE '2026-03-03', 200),
         (5, DATE '2026-03-03', 10)
)
SELECT sale_date,
       SUM(amount) AS day_total,
       SUM(SUM(amount)) OVER (ORDER BY sale_date) AS running_total
FROM orders
GROUP BY sale_date
ORDER BY sale_date;

There is one row per date after the GROUP BY, so ties cannot happen and the default frame is fine. Every column outside the window must be grouped or aggregated, or PostgreSQL stops with column must appear in the GROUP BY clause.

How do I find the first day a running total reaches a target?

Compute the running total in a CTE or subquery and filter it in the outer query. A window function cannot go in the WHERE of the query that computes it, because WHERE runs before window functions. Here the target is 300:

WITH sales(sale_date, amount) AS (
  VALUES (DATE '2026-03-01', 120), (DATE '2026-03-02', 80),
         (DATE '2026-03-03', 200), (DATE '2026-03-04', 50)
),
totals AS (
  SELECT sale_date,
         SUM(amount) OVER (ORDER BY sale_date) AS running_total
  FROM sales
)
SELECT sale_date, running_total
FROM totals
WHERE running_total >= 300
ORDER BY sale_date
LIMIT 1;

Interviewers like this shape: the running total is the easy half, and the filter on it is the part they are testing.

How do I calculate a running total without window functions?

Without window functions, sum every row up to the current one in a correlated subquery. For each row, the subquery adds the amounts with an earlier date, or the same date and a smaller or equal id. The query runs on MySQL 5.7, which has no window functions:

WITH orders(id, order_date, amount) AS (
  VALUES (1, DATE '2026-03-01', 120), (2, DATE '2026-03-02', 80),
         (3, DATE '2026-03-02', 30), (4, DATE '2026-03-03', 200)
)
SELECT o.id, o.order_date, o.amount,
       (SELECT SUM(p.amount)
        FROM orders AS p
        WHERE p.order_date < o.order_date
           OR (p.order_date = o.order_date AND p.id <= o.id)) AS running_total
FROM orders AS o
ORDER BY o.id;

The answer matches the ROWS version, but the subquery reads the earlier rows again for every row, so the work grows with the square of the row count. Use it only where window functions are missing, and the window version everywhere else.

The PostgreSQL tutorial on window functions explains OVER and PARTITION BY, the PostgreSQL window function call syntax defines the default frame, and the MySQL 8.0 window function frames page covers ROWS and RANGE there. More questions of this kind are in the SQL interview practice guide.

Related questions

A rolling or moving average, such as the average of the last seven days, uses the same window with a bounded frame, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, and AVG in place of SUM. Putting running_total >= 300 straight into WHERE fails with “window functions are not allowed in WHERE”, which has its own causes and fixes.