Lesson 12.7 · Reading time: ~6 min

A running total (cumulative sum) adds each value to the sum of all the values before it: revenue to date, account balance after each transaction, cumulative signups. It's a staple of dashboards and a popular SQL interview task. In this lesson you'll calculate it with SUM() OVER (ORDER BY ...), restart it for each group, avoid the trap with duplicate sort values, and see how it was done before window functions.

Running Total in SQL

An ordinary SUM with GROUP BY collapses rows into one total. A running total keeps every row and adds a column with the sum so far. That's exactly what a window function does: SUM(amount) OVER (ORDER BY day) sums all rows from the first one up to the current one, in the order of day. Window functions and frames were covered in Lessons 7.1 and 7.3; here they solve a practical task.

The examples run on the Sakila sample database (MySQL 8.0).

Cumulative Revenue by Day

First, aggregate the payments by day; then add the running total over the daily rows:

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day,
           SUM(amount) AS daily_amount
    FROM payment
    WHERE payment_date >= '2005-08-01'
      AND payment_date < '2005-09-01'
    GROUP BY DATE(payment_date)
)
SELECT pay_day,
       daily_amount,
       SUM(daily_amount) OVER (ORDER BY pay_day) AS running_total
FROM daily
ORDER BY pay_day;

Result: 10 days with payments in August 2005; the running total grows from 2817.29 to 24072.13, the revenue of the whole month.

Aggregating first matters: SUM(...) OVER over individual payments would return one row per payment, not per day. Days without payments are simply missing; to show them with 0, join the data to a calendar (Lesson 6.6).

Restarting the Total for Each Group

PARTITION BY starts a separate running total for each group. Here the monthly revenue gets two cumulative columns: one for all time, and one that restarts every year:

WITH monthly AS (
    SELECT YEAR(payment_date) AS pay_year,
           MONTH(payment_date) AS pay_month,
           SUM(amount) AS monthly_amount
    FROM payment
    GROUP BY YEAR(payment_date), MONTH(payment_date)
)
SELECT pay_year, pay_month, monthly_amount,
       SUM(monthly_amount) OVER (ORDER BY pay_year, pay_month) AS running_total,
       SUM(monthly_amount) OVER (PARTITION BY pay_year ORDER BY pay_month) AS year_to_date
FROM monthly
ORDER BY pay_year, pay_month;

Result: 5 months; the overall running total reaches 67416.51, while the year-to-date total restarts in 2006 at 514.18.

The same pattern gives a running total per customer (PARTITION BY customer_id ORDER BY payment_date) or per store.

The Trap: Equal Sort Values

When ORDER BY in the window has duplicates, the result surprises many people. Two payments in Sakila have exactly the same time:

SELECT payment_id, payment_date, amount,
       SUM(amount) OVER (ORDER BY payment_date) AS default_frame,
       SUM(amount) OVER (ORDER BY payment_date, payment_id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM payment
WHERE payment_date >= '2005-05-30 14:40:00'
  AND payment_date < '2005-05-30 15:00:00'
ORDER BY payment_date, payment_id;

Result: payments 1258 and 8245 both have default_frame = 5.98, while rows_frame shows 2.99 and then 5.98.

With ORDER BY and no explicit frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: it includes all rows with the same sort value as the current one. Rows that tie get the same total. To add rows strictly one by one, use a ROWS frame and a unique tie-breaker in ORDER BY, such as the primary key.

Without Window Functions

Before window functions (MySQL before 8.0), a running total was a correlated subquery: for each row, sum all rows up to it.

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day,
           SUM(amount) AS daily_amount
    FROM payment
    WHERE payment_date >= '2005-08-01'
      AND payment_date < '2005-09-01'
    GROUP BY DATE(payment_date)
)
SELECT d1.pay_day,
       d1.daily_amount,
       (SELECT SUM(d2.daily_amount)
        FROM daily d2
        WHERE d2.pay_day <= d1.pay_day) AS running_total
FROM daily d1
ORDER BY d1.pay_day;

Result: the same 10 rows and totals as the window function version.

For each row, the subquery scans all earlier rows, so the work grows quadratically. Mention it as the fallback; use SUM() OVER in practice.

Practice

• Monthly and Cumulative Payments
• Cumulative Payment Analysis
• Payment Amounts for August 2005

Key Takeaways:

• SUM(x) OVER (ORDER BY key) returns the cumulative sum up to each row and keeps all rows.
• Aggregate to the needed level first (day, month), then add the running total.
• PARTITION BY restarts the total for each group: customer, store, year.
• The default frame is RANGE, so rows with equal sort values share one total; use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and a unique tie-breaker.
• Without window functions, use a correlated subquery, but it is much slower.

Frequently Asked Questions

How do I calculate a running total in SQL?

Use a window function: SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM daily;. It works in MySQL 8.0+, MariaDB 10.2+, PostgreSQL, SQL Server, Oracle and SQLite 3.25+.

How do I calculate a running total per group?

Add PARTITION BY: SUM(amount) OVER (PARTITION BY customer_id ORDER BY payment_date). The sum restarts at the first row of each customer.

Why do some rows have the same running total?

Because they have the same value in the window's ORDER BY column, and the default RANGE frame includes all such peers. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and add a unique column, such as the id, to ORDER BY.

How do I calculate a moving average instead of a running total?

Limit the frame to a fixed number of rows: AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) averages the current day and the six before it. Window frames are covered in Lesson 7.3.