How to Calculate a Running Total in MySQL Without Using Deprecated Variables?

Efficiently Calculating Running Totals in MySQL

MySQL's traditional approach to calculating running totals using variable assignment is now deprecated. This article demonstrates a modern, efficient method to achieve the same result without relying on deprecated features.

A single, optimized query provides a superior solution:

SET @running_total := 0;
SELECT
    DAYOFYEAR(`date`) AS day_of_year,
    COUNT(*) AS daily_count,
    (@running_total := @running_total + COUNT(*)) AS running_total
FROM
    `orders`
WHERE
    `hasPaid` > 0
GROUP BY
    day_of_year
ORDER BY
    day_of_year;

This query initializes a variable, @running_total, to zero. The core functionality lies in the (@running_total := @running_total COUNT(*)) expression. This updates @running_total cumulatively with the COUNT(*) for each day, producing the running total (running_total) alongside the daily count (daily_count) and day of year (day_of_year). The result is a concise and effective running total calculation, eliminating the need for deprecated variable handling.

The above is the detailed content of How to Calculate a Running Total in MySQL Without Using Deprecated Variables?. For more information, please follow other related articles on the PHP Chinese website!