Home >Database >Mysql Tutorial >How to Calculate a Running Total in MySQL Without Using Deprecated Variables?

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

Mary-Kate Olsen
Mary-Kate OlsenOriginal
2025-01-22 06:36:13129browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn