How Can I Calculate Running Totals in MySQL?

MySQL Running Total Calculation: Complete Solution

In MySQL, calculating a running total for a given data set can be achieved in a straightforward way. To do this, consider the following query:

<code class="language-sql">SELECT DAYOFYEAR(`date`) AS d, COUNT(*)
FROM `orders`
WHERE `hasPaid` > 0
GROUP BY d
ORDER BY d</code>

This query returns a table with column (d) for the day of the year and the corresponding count of paid orders for that date. To add a running total column, we can use the variable @runtot and modify the query as follows:

<code class="language-sql">SET @runtot := 0;
SELECT q1.d, q1.c, (@runtot := @runtot + q1.c) AS rt
FROM (SELECT DAYOFYEAR(`date`) AS d, COUNT(*) AS c
    FROM `orders`
    WHERE `hasPaid` > 0
    GROUP BY d
    ORDER BY d) AS q1</code>

In this updated query:

• SET @runtot := 0; Initialize the running total variable to zero.
• Subquery (q1) handles the core calculations, retrieving the day of the year (d) and the count of paid orders (c).
• (@runtot := @runtot q1.c) AS rt Calculate the running total by adding each c value to the previous running total and assign it to the 'rt' column.

The result of this query will be a new table with an additional 'rt' column representing the running total of paid orders for each day.

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