How to Calculate a Running Total of Daily Order Counts in MySQL?

MySQL Running Total Calculation: A Practical Example

Working with tabular data often requires calculating running totals for efficient analysis. This example demonstrates how to calculate a running total of daily order counts within MySQL. We start with a query that counts orders per day:

SELECT DAYOFYEAR(`date`) AS d, COUNT(*) AS daily_count
FROM `orders`
WHERE `hasPaid` > 0
GROUP BY d
ORDER BY d

This yields results similar to:

d	daily_count
20	5
21	7
22	12
23	4

To add a running total column, we use a user variable:

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

This code initializes a variable @running_total to zero. The inner query (q1) selects the day and daily order count. The outer query then iterates through q1, adding each day's count to @running_total and storing the cumulative sum in the running_total column.

The output now includes the running total:

d	daily_count	running_total
20	5	5
21	7	12
22	12	24
23	4	28

This enhanced query provides a running total, simplifying data analysis and visualization.

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