Kirk Ouimet Kirk Ouimet - 5 months ago 29
SQL Question

Create a Cumulative Sum Column in MySQL

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 would look like this:

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


Is there a MySQL update statement that can do this easily? What's the best way to accomplish this?

Answer

If performance is an issue, you could use a MySQL variable:

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

Alternatively, you could remove the cumulative_sum column and calculate it on each query:

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

This calculates the running sum in a running way :)