Forgly

SQL to calculate a running total

Calculate a running total in SQL — a cumulative sum that grows row by row — using the SUM() OVER (ORDER BY ...) window function, with a per-group variant.

PostgreSQL, MySQL 8+, SQL Server, SQLite 3.25+
SELECT order_date,
       amount,
       SUM(amount) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
ORDER BY order_date;
Running total per group (cumulative sum per customer)
SELECT customer_id,
       order_date,
       amount,
       SUM(amount) OVER (
         PARTITION BY customer_id
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
ORDER BY customer_id, order_date;

PARTITION BY customer_id restarts the cumulative sum for each customer, so the running total never carries across groups.

How to calculate a running total (cumulative sum)

A running total (also called a cumulative sum or running sum) adds up values up to and including the current row, ordered by a column such as date. A single window function — SUM(amount) OVER (ORDER BY ...) — computes it in one pass, with no self-join, on PostgreSQL, MySQL 8+, SQL Server, and SQLite 3.25+.

How it works

  • SUM(amount) OVER (...) is a windowed sum — it does not collapse rows like GROUP BY does.
  • ORDER BY order_date defines the direction the total accumulates.
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW limits the sum to all rows from the start up to the current one.
  • Add PARTITION BY customer_id to keep a separate running total per customer (see the second query).

Tip

Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, not the default RANGE frame: with RANGE, rows that tie on the ORDER BY value all share one cumulative value instead of stepping up one row at a time.

Related SQL queries

Frequently asked questions

How do I calculate a running total (cumulative sum) in SQL?

A running total (also called a cumulative sum or running sum) adds up values up to and including the current row, ordered by a column such as date. A single window function — SUM(amount) OVER (ORDER BY ...) — computes it in one pass, with no self-join, on PostgreSQL, MySQL 8+, SQL Server, and SQLite 3.25+. SUM(amount) OVER (...) is a windowed sum — it does not collapse rows like GROUP BY does. ORDER BY order_date defines the direction the total accumulates. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW limits the sum to all rows from the start up to the current one. Add PARTITION BY customer_id to keep a separate running total per customer (see the second query).

Does this work in PostgreSQL, MySQL, SQL Server, and SQLite?

Yes — this page lists the query for each dialect, since the syntax can differ between database engines.

Can I generate this query for my own tables?

Yes. Describe what you want in plain English with Forgly's free AI SQL Generator and it writes the query for your dialect.