Create a Cumulative Sum Column in MySQL

sql

cumulative-sum

mysql

window-functions

byAnton Shumikhin·Oct 30, 2024

Solve cumulative sum problem quickly in MySQL 8.0+ with the power of SUM() and OVER(), providing a concise running total:

SELECT
    id, 
    SUM(value) OVER (ORDER BY id) AS cumulative_sum
FROM
    sales;

For lower versions of MySQL (pre-8.0), use a user-defined variable for the incremental summation:

-- Gentle reminder to declare your variables first. Skipping this, rewards you Debugging Session Ticket.
SET @cumulative := 0;
SELECT
    id, 
    (@cumulative := @cumulative + value) AS cumulative_sum
FROM
    sales
ORDER BY
    id;

Where id, value, and sales are your identifier, the summing field, and table respectively.

Getting results with correlated subquery

A correlated subquery can help you compute a cumulative sum, although be wary it may feel slightly sluggish due to performance:

-- Slow and steady might just win the race here
SELECT 
    sales_primary.id,
    (SELECT SUM(value) FROM sales as sales_secondary WHERE sales_secondary.id <= sales_primary.id) AS cumulative_sum
FROM 
    sales as sales_primary
ORDER BY
    sales_primary.id;

Variables to the rescue (Pre-MySQL 8.0)

Before MySQL hit the golden 8.0, user-defined variables were the go-to allies for the cumulative sum puzzle:

-- Here's a ticket to the most exciting roller coaster ride at Debugging Land, in case the "@total" variable sneaks in unannounced.
SET @total := 0;
SELECT
    id,
    (@total := @total + value) AS cumulative_sum
FROM
    sales
ORDER BY
    id;

Remember to declare the variable via a cross join or the SET clause and ensure the correct order using ORDER BY.

Embracing MySQL 8.0 and beyond

With the advent of MySQL 8.0, new window functions allow elegant and optimised solutions for our trusty cumulative sum:

SELECT
    id,
    SUM(value) OVER (ORDER BY id) as cumulative_sum
FROM
    sales;

So, upgrade to MySQL 8.0, if you haven't. The future loves window functions!

Calculating cumulative sum on-the-go

You don't need to alter the structure of your database every time you need a cumulative sum. Calculate it dynamically in your query using a SELECT statement:

-- We don't need extra columns. We're SQL Ninjas!
SELECT
    id,
    value,
    (@cumulative := @cumulative + value) AS cumulative_sum
FROM
    (SELECT @cumulative := 0) as variable_init,
    sales
ORDER BY
    id;

The magic of `ORDER BY` in cumulative sum

The ordering of rows plays a crucial role in computing cumulative sum. It's the equivalent of a wizard chanting spells in the right sequence. So remember to double-check the ORDER BY clause!

Painless update with subqueries

Sometimes, you want to store the cumulative sum inline. This smart subquery within an UPDATE statement can be a practical way:

-- Multiply your profits without multiplying your tables!
UPDATE sales s1
JOIN (
    SELECT id, (@cumulative := @cumulative + value) AS cumulative_sum
    FROM (SELECT @cumulative := 0) as var_init, sales
    ORDER BY id
) as s2
ON s1.id = s2.id
SET s1.cumulative_sum = s2.cumulative_sum;

Testing: A Must-Do Step

Make sure your query delivers the correct cumulative sum by testing it with sample data:

-- My favourite rubber ducky told me that testing saves future debugging sessions!
SET @total = 0;
INSERT INTO sales VALUES (1,10), (2,20), (3,30), (4,40);
SELECT id, (@total := @total + value) AS cumulative_sum FROM sales ORDER BY id;

Tailoring your query

Ready to customize your queries according to the specific requirements of the scenario? You can modify your variable initializations and use different ORDER BY clauses:

-- As they say, "Variety is the spice of life". Or was it just in SQL?
SET @cumulative := 100;
SELECT id, (@cumulative := @cumulative + value) AS cumulative_sum FROM sales ORDER BY id DESC;