Kirk Ouimet Kirk Ouimet - 1 year ago 110
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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download