Create a Cumulative Sum Column in MySQL

by

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)

More Related Contents:

  1. Cumulative sum over a set of rows in mysql
  2. SQL Print the name of all Employees together with the name of their supervisor
  3. MySQL – Rows to Columns
  4. ROW_NUMBER() in MySQL
  5. MySQL how to fill missing dates in range?
  6. Left Outer Join doesn’t return all rows from my left table?
  7. need to return two sets of data with two different where clauses
  8. SQL statement is ignoring where parameter
  9. quick selection of a random row from a large table in mysql
  10. Join tables with SUM issue in MYSQL
  11. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  12. How does MySQL process ORDER BY and LIMIT in a query?
  13. Generating a random & unique 8 character string using MySQL
  14. sql joins as venn diagram
  15. MySQL, better to insert NULL or empty string?
  16. SQL: Repeat a result row multiple times, and number the rows
  17. Compare dates in MySQL
  18. Invalid default value for ‘dateAdded’
  19. Split a string and loop through values in MySQL stored procedure
  20. How to create dummy variable columns for thousands of categories in Google BigQuery?
  21. how to cast the hexadecimal to varchar(datetime)?
  22. MySQL Select Query – Get only first 10 characters of a value
  23. Most efficient way to store IP Address in MySQL [duplicate]
  24. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  25. mysql select id and name from other table and join query
  26. SQL (MySQL) vs NoSQL (CouchDB) [closed]
  27. DELETE FROM `table` AS `alias` … WHERE `alias`.`column` … why syntax error?
  28. MySQL update statement to store ranking positions
  29. How to convert a varchar column type to date type without losing the dates
  30. MySQL Multiple Joins in one query?

Leave a Comment