How does the running total query work?

GROUP BY collapses the orders to one row per day first, so each row is a day's sales. SUM(SUM(total_amount)) OVER (...) sums those daily totals; the inner SUM is the aggregate, the outer one is the window function. ORDER BY inside OVER sets the order in which rows accumulate, and the ROWS frame runs from the first row to the current one.

What commonly goes wrong with running total in SQL?

Rows with the same ORDER BY value share a total under the default RANGE frame; add a unique tie-breaker and ROWS to get one step per row.

Running Total in SQL

A running total adds each row's value to the sum of all earlier rows, so you can see how a figure builds up over time.

Example query

This example assumes: orders(id, customer_id bigint, order_date date, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT order_date,
       SUM(total_amount) AS daily_sales,
       SUM(SUM(total_amount)) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders
GROUP BY order_date
ORDER BY order_date;

Restart the total for each customer

SELECT customer_id,
       order_date,
       total_amount,
       SUM(total_amount) OVER (
         PARTITION BY customer_id
         ORDER BY order_date, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS customer_running_total
FROM orders
ORDER BY customer_id, order_date, id;

How it works

GROUP BY collapses the orders to one row per day first, so each row is a day's sales.
SUM(SUM(total_amount)) OVER (...) sums those daily totals; the inner SUM is the aggregate, the outer one is the window function.
ORDER BY inside OVER sets the order in which rows accumulate, and the ROWS frame runs from the first row to the current one.

MySQL, BigQuery, SQL Server and SQLite differences

MySQL 8+, BigQuery, SQL Server 2012+ and SQLite 3.25+ support the same window syntax. MySQL 5.7 has no window functions.
Without an explicit ROWS frame, most databases default to RANGE, which treats rows with the same ORDER BY value as one group.

Common mistakes

Rows with the same ORDER BY value share a total under the default RANGE frame; add a unique tie-breaker and ROWS to get one step per row.
Days with no orders are missing, so the series skips them; fill the dates first if you need a continuous line.
A running total across all history is rarely what a monthly report needs; restart it with PARTITION BY the period.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

Show me the running total of sales by day this quarter.
What is the cumulative revenue for each customer over time?