Create a column for cumulative sum in MySQL

Question

I have a table that looks like this:

id count
1 100
2 50
3 10

I want to add a new column called cumulative_sum, so the table looks like this:

id count cumulative_sum
1 100 100
2 50 150
3 10 160

Is there a MySQL update statement that can easily achieve this? What's the best approach?

Answer 1

Use related queries:

---

SELECT t.id,
         t.count,
         (SELECT SUM(x.count)
            FROM TABLE x
           WHERE x.id <= t.id) AS cumulative_sum
    FROM TABLE t
ORDER BY t.id

Use MySQL variables:

---

SELECT t.id,
         t.count,
         @running_total := @running_total + t.count AS cumulative_sum
    FROM TABLE t
    JOIN (SELECT @running_total := 0) r
ORDER BY t.id

Notice:

• JOIN (SELECT @running_total := 0) r is a cross join that allows variables to be declared without the need for a separate SET command.
• MySQL requires a table alias for any subquery/derived table/inline viewr

Precautions:

• Applies to MySQL only, not portable to other databases
• ORDER BY is very important, it ensures that the order matches the original question, and may have a greater impact for more complex variable usage (eg: pseudo-ROW_NUMBER/RANK functionality, not supported by MySQL)

Answer 2

If performance is an issue, you can use MySQL variables:

set @csum := 0;
update YourTable
set cumulative_sum = (@csum := @csum + count)
order by id;

Alternatively, you can remove the cumulative_sum column and calculate it in every query:

set @csum := 0;
select id, count, (@csum := @csum + count) as cumulative_sum
from YourTable
order by id;

This calculates the cumulative sum in a continuous manner :)