How to Calculate Running Totals in MySQL with a Single Query?

Efficiently Calculating Running Totals in MySQL with a Single Query

This guide demonstrates a single MySQL query to calculate running totals for daily order counts, assuming your data includes a date column and a hasPaid flag indicating successful payment.

Here's the optimized query:

SET @runtot:=0;
SELECT
   DATE(`date`) AS d,
   COUNT(*) AS c,
   (@runtot := @runtot + COUNT(*)) AS rt
FROM  `orders`
WHERE  `hasPaid` > 0
GROUP  BY d
ORDER  BY d;

This query initializes a variable @runtot to zero. The core logic then calculates the daily order count (COUNT(*) AS c) and simultaneously updates the running total (@runtot := @runtot COUNT(*) AS rt) within a single SELECT statement. This avoids the need for subqueries or multiple passes, resulting in improved efficiency. The GROUP BY d clause groups results by day, and ORDER BY d ensures the running total is calculated sequentially. This method efficiently generates the running total column (rt) alongside the daily order count (c) and day (d).

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