SQL Query to Calculate Cumulative Sum by Group

Have you ever needed to track a running total in your data — sales that accumulate day by day, payments that build up customer by customer, or inventory that grows transaction by transaction?

In most cases, you might try to calculate this with a subquery for every row — joining the table back to itself, summing all previous rows each time, repeating that logic for every group in your data. Or you might export the data to Excel and calculate the running totals there manually.

This is slow, hard to read, and breaks completely when your data grows.

SQL cumulative sum using window functions solves this in a single, elegant query. You tell SQL which column to sum, which group to reset the total for, and which order to accumulate in and SQL calculates the exact running total for every row automatically, no self-joins or subqueries required.

In this guide, we will break down SQL cumulative sum completely. What it is, how to write it step by step, real-world examples across different scenarios, and when to use it versus other aggregation approaches.

What Is a Cumulative Sum in SQL?

A cumulative sum (also called a running total) is a calculation where each row’s value shows the sum of all values up to and including that row in a specified order, optionally resetting at the boundary of each group.

It answers the question: “At this point in the sequence, what is the total so far — for this group?”

Simple Analogy

Imagine you are keeping score at a bowling tournament. After each frame, you do not just record that frame’s pins — you record the running total. Frame 1: 7 pins → total 7. Frame 2: 9 pins → total 16. Frame 3: 8 pins → total 24. And when the next player starts, the running total resets to zero for them.

SQL cumulative sum does exactly this for any numeric column in your data grouped by any category, ordered by any sequence regardless of how many rows or groups you have.

The Three Components of a Cumulative Sum Query

Every cumulative sum in SQL requires three things:

• The value to sum — The numeric column you are accumulating (sales amount, quantity, payment, etc.)
• The partition — The group boundary where the running total resets (customer, region, product, etc.)
• The order — The sequence in which values are accumulated (date, transaction ID, row number, etc.)

Where to Find This in SQL

Cumulative sum uses the window function syntax available in all major SQL databases:

• PostgreSQL — Full support
• MySQL 8.0+ — Full support
• SQL Server — Full support
• SQLite 3.25+ — Full support
• BigQuery — Full support
• Snowflake — Full support
• Oracle — Full support (uses same syntax)

MySQL 5.x and earlier does not support window functions. See the workaround in the Limitations section.

The core function is:

sql

SUM(column) OVER (PARTITION BY group_column ORDER BY order_column)

Step-by-Step: Your First Cumulative Sum Query

Example 1: Running Total of Daily Sales by Region

Setup — a sales table:

sale_date	region	amount
2024-01-01	North	1000
2024-01-02	North	1500
2024-01-03	North	1200
2024-01-01	South	800
2024-01-02	South	950
2024-01-03	South	1100

You want a running total of sales for each region, accumulating day by day and resetting when the region changes.

Step 1: Select your base columns

sql

SELECT
    sale_date,
    region,
    amount
FROM sales

Start with the columns you need before adding the window function.

Step 2: Add the SUM window function

sql

SELECT
    sale_date,
    region,
    amount,
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY sale_date
    ) AS cumulative_sales
FROM sales

Step 3: Understand each clause

• SUM(amount) — the value being accumulated
• OVER (...) — signals this is a window function, not a regular aggregate
• PARTITION BY region — reset the running total for each region
• ORDER BY sale_date — accumulate in date order within each region

Step 4: Check the result

sale_date	region	amount	cumulative_sales
2024-01-01	North	1000	1000
2024-01-02	North	1500	2500
2024-01-03	North	1200	3700
2024-01-01	South	800	800
2024-01-02	South	950	1750
2024-01-03	South	1100	2850

The running total resets at South because PARTITION BY region creates a separate window for each region. Within each region, values accumulate in date order.

Step 5: Add ORDER BY to your final query for clean output

sql

SELECT
    sale_date,
    region,
    amount,
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY sale_date
    ) AS cumulative_sales
FROM sales
ORDER BY region, sale_date;

The ORDER BY inside OVER() controls accumulation order. The ORDER BY at the end of the query controls display order. They are independent — always include both for predictable results.

How SQL Cumulative Sum Works Internally

When SQL encounters a window function, it does not collapse rows like a regular GROUP BY aggregate. Instead, it processes each row in the context of a window — a defined set of rows relative to the current row.

For SUM(amount) OVER (PARTITION BY region ORDER BY sale_date), SQL builds the following frame for each row:

• Partition boundary — Only rows where region matches the current row’s region are included
• Default frame — ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW from the first row of the partition to the current row, in the specified order

This means for the North region on Jan 3: SQL looks at all North rows from the earliest date up to Jan 3 and sums their amounts: 1000 + 1500 + 1200 = 3700.

The Window Frame Explained

sql

SUM(amount) OVER (
    PARTITION BY region
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- default, usually implicit
)

Frame Clause	Meaning
UNBOUNDED PRECEDING	From the first row of the partition
CURRENT ROW	Up to and including the current row
UNBOUNDED FOLLOWING	To the last row of the partition
N PRECEDING	N rows before the current row
N FOLLOWING	N rows after the current row

The default frame for SUM() OVER (... ORDER BY ...) is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — which is exactly what cumulative sum needs. You rarely need to specify the frame explicitly for running totals.

Real-World Examples

Example 2: Cumulative Revenue per Customer

Goal: Track the total amount each customer has spent across all their orders, accumulating by order date.

sql

SELECT
    customer_id,
    order_date,
    order_amount,
    SUM(order_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS lifetime_spend
FROM orders
ORDER BY customer_id, order_date;

Result:

customer_id	order_date	order_amount	lifetime_spend
C001	2024-01-05	250	250
C001	2024-02-12	180	430
C001	2024-03-08	320	750
C002	2024-01-18	90	90
C002	2024-02-25	415	505

Each customer’s lifetime spend resets to zero and accumulates independently. This is the foundation of customer lifetime value (CLV) analysis — you can see exactly when a customer crossed any spend threshold.

Example 3: Running Inventory Balance

Goal: Calculate the current inventory level after each transaction — adding stock received and subtracting stock sold.

sql

SELECT
    product_id,
    transaction_date,
    transaction_type,
    quantity,
    SUM(
        CASE
            WHEN transaction_type = 'received' THEN quantity
            WHEN transaction_type = 'sold'     THEN -quantity
            ELSE 0
        END
    ) OVER (
        PARTITION BY product_id
        ORDER BY transaction_date, transaction_id
    ) AS running_balance
FROM inventory_transactions
ORDER BY product_id, transaction_date;

Result:

product_id	transaction_date	transaction_type	quantity	running_balance
P001	2024-01-01	received	100	100
P001	2024-01-03	sold	30	70
P001	2024-01-07	received	50	120
P001	2024-01-10	sold	45	75

The CASE statement inside SUM() converts received quantities to positive and sold quantities to negative before accumulating — producing a true running balance. Including transaction_id in the ORDER BY ensures stable ordering when multiple transactions share the same date.

Example 4: Cumulative Sum Without Grouping (Grand Running Total)

Goal: Calculate a single running total across all rows — no group reset.

Simply omit PARTITION BY:

sql

SELECT
    sale_date,
    region,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date, sale_id
    ) AS grand_running_total
FROM sales
ORDER BY sale_date, sale_id;

Without PARTITION BY, the entire result set is treated as one window — the running total never resets and accumulates across all rows in the specified order.

Example 5: Cumulative Percentage of Total by Group

Goal: Show each row’s cumulative sales as a percentage of the group total — useful for Pareto analysis.

sql

SELECT
    region,
    product,
    sales,
    SUM(sales) OVER (PARTITION BY region ORDER BY sales DESC) AS cumulative_sales,
    SUM(sales) OVER (PARTITION BY region)                     AS total_regional_sales,
    ROUND(
        100.0 * SUM(sales) OVER (PARTITION BY region ORDER BY sales DESC)
              / SUM(sales) OVER (PARTITION BY region),
        2
    ) AS cumulative_pct
FROM product_sales
ORDER BY region, sales DESC;

Result:

region	product	sales	cumulative_sales	total_regional_sales	cumulative_pct
North	Laptop	45000	45000	80000	56.25
North	Monitor	22000	67000	80000	83.75
North	Keyboard	13000	80000	80000	100.00

Two window functions work together here:

• SUM(...) OVER (PARTITION BY region ORDER BY sales DESC) — cumulative sum in descending sales order
• SUM(...) OVER (PARTITION BY region) — no ORDER BY, so this sums the entire partition (total for the region)

Dividing the first by the second gives the cumulative percentage — classic Pareto / 80-20 analysis in pure SQL.

Example 6: Monthly Cumulative Sales With Year Reset

Goal: Calculate a running total of sales that accumulates month by month but resets at the start of each year — for year-to-date (YTD) reporting.

sql

SELECT
    EXTRACT(YEAR  FROM sale_date) AS sale_year,
    EXTRACT(MONTH FROM sale_date) AS sale_month,
    monthly_sales,
    SUM(monthly_sales) OVER (
        PARTITION BY EXTRACT(YEAR FROM sale_date)
        ORDER BY     EXTRACT(MONTH FROM sale_date)
    ) AS ytd_sales
FROM monthly_sales_summary
ORDER BY sale_year, sale_month;

Result:

sale_year	sale_month	monthly_sales	ytd_sales
2023	1	12000	12000
2023	2	15000	27000
2023	3	11000	38000
2024	1	14000	14000
2024	2	18000	32000

PARTITION BY EXTRACT(YEAR FROM sale_date) resets the running total at the start of each calendar year — producing a true year-to-date accumulation that is immediately useful in financial dashboards and monthly business reviews.

Cumulative Sum Across Multiple Groups Simultaneously

You can calculate cumulative sums for different groupings in the same query by using multiple window functions in the SELECT clause:

sql

SELECT
    sale_date,
    region,
    salesperson,
    amount,
    -- Running total by region
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY sale_date
    ) AS region_running_total,
    -- Running total by salesperson
    SUM(amount) OVER (
        PARTITION BY salesperson
        ORDER BY sale_date
    ) AS salesperson_running_total,
    -- Grand running total (no partition)
    SUM(amount) OVER (
        ORDER BY sale_date, sale_id
    ) AS grand_running_total
FROM sales
ORDER BY sale_date;

Each OVER() clause defines an independent window — they do not interfere with each other. SQL calculates all three simultaneously in a single pass through the data.

Cumulative Sum vs Other SQL Aggregations

Both GROUP BY aggregates and window functions sum data but they serve fundamentally different purposes.

GROUP BY with SUM:

• Collapses multiple rows into one row per group
• Returns the total for each group — not row-level detail
• Cannot show individual transactions alongside their group total
• Best for: summary reports, total-per-group calculations

Window function SUM() OVER():

• Preserves every row — no collapsing
• Calculates a value relative to each row’s position in its window
• Can show individual transactions alongside running totals and group totals in the same row
• Best for: running totals, row-level context with group-level aggregates, time-series accumulation

Feature	GROUP BY SUM	SUM() OVER()
Preserves row detail	Collapses rows	Every row kept
Running total	Not possible	Core use case
Group total on each row	Not possible	Omit ORDER BY
Resets per group	Implicit	PARTITION BY
Filters rows	HAVING clause	Use outer WHERE
Complexity	Simple	Moderate

Use GROUP BY when you need one summary row per group. Use window functions when you need row-level detail alongside cumulative or group-level values.

Common Limitations

MySQL 5.x Has No Window Function Support

MySQL versions before 8.0 do not support OVER(). Use a correlated subquery workaround:

sql

-- MySQL 5.x workaround (slow on large tables)
SELECT
    a.sale_date,
    a.region,
    a.amount,
    (
        SELECT SUM(b.amount)
        FROM sales b
        WHERE b.region = a.region
          AND b.sale_date <= a.sale_date
    ) AS cumulative_sales
FROM sales a
ORDER BY a.region, a.sale_date;

This works but performs a full subquery per row — avoid on large datasets. Upgrade to MySQL 8.0+ when possible.

Ties in ORDER BY Produce Non-Deterministic Results

If multiple rows share the same ORDER BY value (e.g., two transactions on the same date), SQL may include both in the running total or only one — depending on the frame definition and database engine. Always include a unique tie-breaker column like a transaction ID:

sql

ORDER BY sale_date, transaction_id  -- tie-breaker ensures stable results

Window Functions Cannot Be Used in WHERE or HAVING

You cannot filter on a window function result directly:

sql

-- WRONG — this will error
SELECT ..., SUM(amount) OVER (...) AS running_total
FROM sales
WHERE running_total > 5000;  -- cannot reference window function here

Wrap the query in a subquery or CTE:

sql

-- CORRECT
WITH running AS (
    SELECT
        sale_date,
        region,
        amount,
        SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total
    FROM sales
)
SELECT *
FROM running
WHERE running_total > 5000;

Performance on Very Large Tables

Window functions with ORDER BY inside OVER() require sorting which can be expensive on tens of millions of rows without proper indexing. Index the partition and order columns:

sql

CREATE INDEX idx_sales_region_date ON sales(region, sale_date);

This allows the database to avoid a full sort and dramatically speeds up window function execution.

Common Mistakes to Avoid

Confusing the inner ORDER BY with the outer ORDER BY — The ORDER BY inside OVER() defines accumulation order. The ORDER BY at the end of the query defines display order. They are completely independent. Omitting the inner ORDER BY gives you a group total on every row — not a running total.

Omitting a tie-breaker in ORDER BY — If two rows share the same order value (same date, same ID), results may vary between runs or database engines. Always add a unique column like transaction_id as a secondary sort to guarantee stable, deterministic results.

Expecting PARTITION BY to filter rows — PARTITION BY does not remove rows from the result. It only defines where the running total resets. All rows still appear in the output. Use WHERE to filter rows.

Trying to filter on window function results in the same query’s WHERE — Window functions are evaluated after WHERE and GROUP BY. You cannot reference them in WHERE or HAVING in the same query level. Always wrap in a CTE or subquery to filter on window function output.

Using ROWS vs RANGE frames incorrectly — ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW counts physical rows. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW groups all rows with the same ORDER BY value. For running totals, ROWS is almost always what you want — RANGE can produce unexpected results when ORDER BY values are not unique.

Forgetting that NULL values affect SUM — SUM() ignores NULL values, but if your ordering column contains NULL, row ordering becomes unpredictable. Filter out or handle NULL values in the ORDER BY column before running cumulative sum queries.

Cumulative Sum Cheat Sheet

Scenario	Query Pattern
Running total by group	SUM(col) OVER (PARTITION BY grp ORDER BY dt)
Grand running total (no reset)	SUM(col) OVER (ORDER BY dt)
YTD by year	PARTITION BY EXTRACT(YEAR FROM dt) ORDER BY EXTRACT(MONTH FROM dt)
Running inventory balance	SUM(CASE WHEN type='in' THEN qty ELSE -qty END) OVER (...)
Cumulative % of group total	SUM(col) OVER (PARTITION BY grp ORDER BY col DESC) / SUM(col) OVER (PARTITION BY grp)
Multiple running totals	Multiple SUM() OVER() clauses in same SELECT
Filter on running total	Wrap in CTE, then WHERE running_total > value
Stable results with date ties	Add unique ID as tie-breaker: ORDER BY dt, id

SQL cumulative sum using window functions is one of the most useful techniques in data analysis — it turns a flat table of transactions into a meaningful time-series story that shows how values build up over time, by group.

Here is the simplest summary of everything we covered:

• Cumulative sum accumulates values row by row in a defined order, optionally resetting per group
• The syntax is SUM(column) OVER (PARTITION BY group ORDER BY sequence)
• PARTITION BY defines where the running total resets — omit it for a grand running total
• ORDER BY inside OVER() defines accumulation order — not display order
• Window functions preserve every row — unlike GROUP BY which collapses them
• Filter on window function results using a CTE or subquery
• Always add a tie-breaker to ORDER BY when dates or IDs may repeat
• Index partition and order columns for performance on large tables

Start with a single running total — daily sales accumulating by date. Once that is comfortable, add a PARTITION BY to reset per group, then combine multiple window functions in one query. Within a short time, cumulative sum will become one of your most-reached-for tools in any analytical SQL query.

FAQs