ANSI SQL / UniversalWindow Functions

How to Calculate a Running Total (Cumulative Sum) in SQL

Compute cumulative running totals over time or partitioned groups using SUM() OVER (ORDER BY ...).

Quick SQL Solution

Safe (Read / Non-destructive)ANSI SQL / UniversalIntermediate

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date ASC) AS running_total
FROM orders;

When to use this scenario:You are building a revenue dashboard or financial ledger and need the accumulated revenue balance for each transaction row.

Step-by-Step Query Breakdown

1Basic running total across entire table

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date ASC) AS running_total
FROM orders;

Calculates the cumulative sum of amount ordered by date from the first row up to the current row.

2Running total partitioned by customer

SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date ASC
  ) AS customer_cumulative_spend
FROM orders;

Resets the running total back to 0 whenever the customer_id changes.

3Running monthly revenue with CTE

WITH monthly_sales AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS monthly_revenue
  FROM orders
  GROUP BY 1
)
SELECT
  month,
  monthly_revenue,
  SUM(monthly_revenue) OVER (ORDER BY month ASC) AS cumulative_annual_revenue
FROM monthly_sales;

Aggregates monthly sums first, then calculates the cumulative running revenue.

Critical SQL Pitfalls & Precautions

•If multiple rows have the exact same ORDER BY value, SUM() OVER will calculate the sum including all peer rows with the same value unless you include an ID tie-breaker (ORDER BY date, id).

Frequently Asked Questions

Running Total (Window Function) - Frequently Asked Questions

Common questions about transactional safety, indexing, and engine compatibility.

Yes! SQLite added full window function support in version 3.25.0+.

Related SQL Query Guides

Browse All SQL Recipes

ANSI SQL / UniversalSafe

How to Find and Delete Duplicate Rows in SQL

Identify rows with duplicate column values using GROUP BY and HAVING count(*) > 1, and safely remove duplicates using ROW_NUMBER() CTEs.

ANSI SQL / UniversalDestructive

DELETE vs TRUNCATE vs DROP in SQL: Differences Explained

Compare row-by-row logging (DELETE), fast table emptying with auto-increment reset (TRUNCATE), and complete table removal (DROP).

PostgreSQLSafe

How to UPSERT in PostgreSQL Using ON CONFLICT DO UPDATE

Atomically insert a new row or update an existing row if a unique constraint or primary key conflict is encountered.

PostgreSQLSafe

How to Create an Index Concurrently in PostgreSQL (Zero Downtime)

Build a production database index on a table with millions of rows without locking out live INSERT, UPDATE, or DELETE operations.

ANSI SQL / UniversalReversible

How to UPDATE a Table From Another Table in SQL

Update values in one table based on matching columns or aggregations in a secondary table.

ANSI SQL / UniversalReversible

How to Reset Auto Increment / Sequence in MySQL & PostgreSQL

Reset the next auto-generated primary key ID back to 1 or synchronize it with the maximum existing ID in the table.