How to Calculate Cumulative Sum in MySQL Without Analytic Functions?

Cumulative Sum Calculation for a Set of Rows in MySQL

Problem Statement:

Determine the cumulative sum for each ID across all hours of the day from a query result set containing columns for ID, day, hour, and amount.

Answer:

SELECT IF(@prev_id = c.id AND @prev_day = c.day
         ,@cumtotal := @cumtotal + c.amount
         ,@cumtotal := c.amount) AS cumulative_total
     , @prev_id  := c.id  AS `id`
     , @prev_day := c.day AS `day`
     , c.hr
     , c.amount AS `amount'
  FROM ( SELECT @prev_id  := NULL
              , @prev_day := NULL
              , @subtotal := 0
       ) i
  JOIN (

         select id, day, hr, amount from
         ( //multiple joins on multiple tables)a
         left join
         (//unions on multiple tables)b
         on a.id=b.id

         ORDER BY 1,2,3
       ) c

Additional Notes:

• MySQL versions prior to 8.0 lack analytic functions for cumulative sum calculation.
• The provided solution utilizes MySQL user variables and control break processing to emulate analytic functionality.
• The query has been modified to order rows by ID, day, and hour for correct cumulative sum computation.
• The computed cumulative total column is returned as the first column in the result set.

The above is the detailed content of How to Calculate Cumulative Sum in MySQL Without Analytic Functions?. For more information, please follow other related articles on the PHP Chinese website!