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