mysql分类并计算总额,在MySQL中计算运行总计

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?

解决方案

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.