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