Cumulative Sum in MySQL

[anika1]

Cumulative Sums are a little tricky in MySQL.

The simplest way to tackle this would be to use the running_total measure, or utilize the running_total() function in Table Calculations.

If you need to be able to reference your running total or sum in other fields, these approaches won’t do it. In this case, you could try something like the following:

      SELECT 
        orders.user_id AS user_id
        , DATE_FORMAT(orders.created_at,'%Y-%m') AS orders_created_month
        , SUM(orders.amount) AS this_month_sum
        , ( 
              SELECT SUM(o.amount)
              FROM orders AS o
              WHERE o.user_id = orders.user_id
              AND o.id <= orders.id
          ) AS cumulative_sum
      FROM orders
      GROUP BY 1,2
      ORDER BY 1,2,3

Adapted from this StackOverflow Article.