Home >Database >Mysql Tutorial >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:
<code class="language-sql">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;</code>
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!