mysql 累计和,从MySQL表中获取累计和

I have a table containing donations, and I am now creating a page to view statistics. I would like to fetch monthly data from the database with gross and cumulative gross.

mysql> describe donations;

+------------------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+------------------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| transaction_id | varchar(64) | NO | UNI | | |

| donor_email | varchar(255) | NO | | | |

| net | double | NO | | 0 | |

| gross | double | NO | | NULL | |

| original_request | text | NO | | NULL | |

| time | datetime | NO | | NULL | |

| claimed | tinyint(4) | NO | | NULL | |

+------------------+------------------+------+-----+---------+----------------+

Here's what I've tried:

SET @cgross = 0;

SELECT YEAR(`time`), MONTH(`time`), SUM(`gross`), (@cgross := @cgross + SUM(`gross`)) AS `cumulative_gross` FROM `donations` GROUP BY YEAR(`time`), MONTH(`time`);

The result is:

+--------------+---------------+--------------+------------------+

| YEAR(`time`) | MONTH(`time`) | SUM(`gross`) | cumulative_gross |

+--------------+---------------+--------------+------------------+

| 2013 | 1 | 257 | 257 |

| 2013 | 2 | 140 | 140 |

| 2013 | 3 | 311 | 311 |

| 2013 | 4 | 279 | 279 |

+--------------+---------------+--------------+------------------+

Which is wrong. The desired result would be:

+--------------+---------------+--------------+------------------+

| YEAR(`time`) | MONTH(`time`) | SUM(`gross`) | cumulative_gross |

+--------------+---------------+--------------+------------------+

| 2013 | 1 | 257 | 257 |

| 2013 | 2 | 140 | 397 |

| 2013 | 3 | 311 | 708 |

| 2013 | 4 | 279 | 987 |

+--------------+---------------+--------------+------------------+

I tried this without SUM, and it did work as expected.

SET @cgross = 0;

SELECT YEAR(`time`), MONTH(`time`), SUM(`gross`), (@cgross := @cgross + 10) AS `cumulative_gross` FROM `donations` GROUP BY YEAR(`time`), MONTH(`time`);

+--------------+---------------+--------------+------------------+

| YEAR(`time`) | MONTH(`time`) | SUM(`gross`) | cumulative_gross |

+--------------+---------------+--------------+------------------+

| 2013 | 1 | 257 | 10 |

| 2013 | 2 | 140 | 20 |

| 2013 | 3 | 311 | 30 |

| 2013 | 4 | 279 | 40 |

+--------------+---------------+--------------+------------------+

Why doesn't it work with SUM? Any ideas how I could fix it?

Thanks,

Lassi

解决方案

A subquery without variables will do it just as easily, and quite a bit more portably;

SELECT YEAR(`time`),

MONTH(`time`),

SUM(gross),

(SELECT SUM(gross)

FROM donations

WHERE `time`<=MAX(a.`time`)) cumulative_gross

FROM donations a GROUP BY YEAR(`time`), MONTH(`time`);