How to Calculate Running Totals in MySQL Versions Prior to 8.0?

Calculating Cumulative Sum over Row Sets in MySQL

You are faced with a task to obtain a running aggregate for a specific column across a set of rows in MySQL. The challenge lies in the lack of built-in analytic functions in MySQL prior to version 8.0.

Approach Using User Variables

In this situation, you can employ user variables to emulate the functionality of analytic functions. This involves wrapping your original query in another query and utilizing ORDER BY to process rows sequentially.

The outer query checks if the current row's values for id and day match those of the previous row. If so, the cumulative total is updated by adding the amount from the current row. If not, the cumulative total is reset and set to the current row's amount.

Example Query

Here's an example query that demonstrates this approach:

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

By wrapping this entire query in parentheses and using it as an inline view, you can reorder the columns as desired:

SELECT d.id
     , d.day
     , d.hr
     , d.amount
     , d.cumulative_total
FROM (
       // query from above
     ) d

Limitations

Keep in mind that this approach can be computationally intensive for large datasets. Additionally, if the predicates in the original query are complex, it can further increase the complexity of the solution.

The above is the detailed content of How to Calculate Running Totals in MySQL Versions Prior to 8.0?. For more information, please follow other related articles on the PHP Chinese website!