Create a Cumulative Sum Column 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 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 1

Using a correlated query:

---

  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 

Using 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 

Note:

• The JOIN (SELECT @running_total := 0) r is a cross join, and allows for variable declaration without requiring a separate SET command.
• The table alias, r, is required by MySQL for any subquery/derived table/inline view

Caveats:

• MySQL specific; not portable to other databases
• The ORDER BY is important; it ensures the order matches the OP and can have larger implications for more complicated variable usage (IE: psuedo ROW_NUMBER/RANK functionality, which MySQL lacks)