Calculate a running total in MySQL

Question

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?

Answer 1

Perhaps a simpler solution for you and prevents the database having to do a ton of queries. This executes just one query then does a little math on the results in a single pass.

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 

This will give you an additional RT (running total) column. Don't miss the SET statement at the top to initialize the running total variable first or you will just get a column of NULL values.