Create a column for cumulative sum in MySQL

Question

I have a table that looks like this: idcount1100250310 I want to add a new column called cumulative_sum, so the table looks like this: idcountcumulative_sum1100100250150310160 Is there a MySQL update statement that can easily achieve this? What's the best approach?

P粉245276769 · Answer

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)

P粉006540600 · Answer

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 :)