Calculate a running total in MySQL

nickf_9021 0
Viewed 73198

I have this MySQL query:

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

Which returns something like this:

d  | COUNT(*) |
20 |  5       |
21 |  7       |
22 | 12       |
23 |  4       |

What I'd really like is another column on the end to show the running total:

d  | COUNT(*) | ??? |
20 |  5       |   5 |
21 |  7       |  12 |
22 | 12       |  24 |
23 |  4       |  28 |

Is this possible?

mysql sql
5 Answers
SELECT 
   DAYOFYEAR(O.`date`)  AS d, 
   COUNT(*),
   (select count(*) from `orders` 
       where  DAYOFYEAR(`date`) <= d and   `hasPaid` > 0)
FROM  
  `orders` as O
WHERE  
  O.`hasPaid` > 0
GROUP  BY d
ORDER  BY d

This will require some syntactical tuning (I don't have MySQL to test it), but it shows you the idea. THe subquery just has to go back and add up everything fresh that you already included in the outer query, and it has to do that for every row.

Take a look at this question for how to use joins to accomplish the same.

To address concerns about performance degradation with growing data: Since there are max. 366 days in a year, and I assume that you are not running this query against multiple years, the subquery will get evaluated up to 366 times. With proper indices on the date and the hasPaid flag, you'll be ok.

cdonner_588800

Starting with MySQL 8, you will be using window functions for this kind of query:

SELECT dayofyear(`date`) AS d, count(*), sum(count(*)) OVER (ORDER BY dayofyear(`date`))
FROM `orders`
WHERE `hasPaid` > 0
GROUP BY d
ORDER BY d

In the above query, the aggregate function count(*) is nested inside of the window function sum(..) OVER (..), which is possible because of the logical order of operations in SQL. If that's too confusing, you can easily resort to using a derived table or a WITH clause to better structure your query:

WITH daily (d, c) AS (
  SELECT dayofyear(`date`) AS d, count(*)
  FROM `orders`
  WHERE `hasPaid` > 0
  GROUP BY d
)
SELECT d, c, sum(c) OVER (ORDER BY d)
ORDER BY d
user_5217990

It is possible to calculate a running balance using a temporary table in MySQL. The following query should work:

CREATE TEMPORARY table orders_temp1 (SELECT id, DAYOFYEAR(`date`)  AS d, COUNT(*) as total FROM  `orders` WHERE  `hasPaid` > 0 GROUP BY d ORDER  BY d);
CREATE TEMPORARY table orders_temp2 (SELECT * FROM orders_temp1);
SELECT d, total, (SELECT SUM(t2.total) FROM orders_temp2 t2 WHERE t2.id<=t1.id) as running_total FROM orders_temp1 t1;

A temporary table is used for organizing the query. Note that a temporary table only exists for the duration of the connection to the MySQL server

The above query uses a sub query, which returns balance of all rows in the temporary table upto and including the current row. The balance is assigned to the current row in the actual table

user_45085930

I would say that this is impossible every resulting row should be independent. Use programming language for getting these values

user_797080
Related