How to Efficiently Calculate Running Totals in MySQL?

MySQL running total: solution using user variables

This article, originally titled "Calculating a running total in MySQL," is intended to add a column to a table that displays the cumulative sum of previous column values. However, the answer provided states that using user variables in expressions is now deprecated and will be removed in a future version. Therefore, we offer a simpler solution that involves initializing a running total variable and executing a single query using mathematical calculations to achieve the desired result.

The queries provided are as follows:

<code class="language-sql">SET @runtot:=0;
SELECT
   q1.d,
   q1.c,
   (@runtot := @runtot + q1.c) AS rt
FROM
   (SELECT
       DAYOFYEAR(`date`) AS d,
       COUNT(*) AS c
    FROM  `orders`
    WHERE  `hasPaid` > 0
    GROUP  BY d
    ORDER  BY d) AS q1</code>

This query first initializes the user variable @runtot to 0 using a SET statement. It then executes a subquery to retrieve the necessary data from the orders table. Finally, the outer query combines the results of the subquery with a running total calculation.

The resulting table will contain an additional column called rt that represents the running total for column c for each row. This solution avoids the use of deprecated user variables in expressions and provides a more efficient approach with a single query execution.

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