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)