I have a table ("dump") with transactions, and I want to list the total amount, grouped by category, per month, like: Month | Category | Category ID | SUM. The tables involved looks like this:
TABLE dump: id INT date DATE event VARCHAR(100) amount DECIMAL(10, 2)
TABLE dump_cat: id INT did INT (id in dump) cid INT (id in categories)
TABLE categories: id INT name VARCHAR(100)
Now the query I'm trying to use is:
SELECT SUBSTR(d.date,1,7) AS month, c.name, c.id AS catid, SUM(d.amount) AS sum FROM dump as d, dump_cat as dc, categories AS c WHERE dc.did = d.id AND c.id = dc.cid AND SUBSTR(d.date, 1, 7) >= '2008-08' GROUP BY month, c.name ORDER BY month;
But the sum for most categories is twice as big as it should be. My guess is that this is because the join returns multiple rows, but adding "DISTINCT d.id" in the field part doesn't make any difference. An example of what the query returns is:
+---------+--------------------------+-------+-----------+ | month | name | catid | sum | +---------+--------------------------+-------+-----------+ | 2008-08 | Cash | 21 | -6200.00 | | 2008-08 | Gas | 8 | -2936.19 | | 2008-08 | Rent | 1 | -15682.00 |
where as
SELECT DISTINCT d.id, d.amount FROM dump AS d, dump_cat AS dc WHERE d.id = dc.did AND SUBSTR(d.date, 1, 7) ='2008-08' AND dc.cid = 21;
returns
+------+----------+ | id | amount | +------+----------+ | 3961 | -600.00 | | 2976 | -200.00 | | 2967 | -400.00 | | 2964 | -200.00 | | 2957 | -300.00 | | 2962 | -1400.00 | +------+----------+
That makes a total of 3100, half of the sum listed above. If I remove "DISTINCT d.id" from the last query, every row is listed twice. This I think is the problem, but I need help to figure out how to solve it. Thanks in advance.
Added: If I collect the dump and dump_cat tables into one, with
CREATE table dumpwithcat SELECT DISTINCT d.id, d.date, d.event, d.amount, dc.cid FROM dump AS d, dump_cat AS c WHERE c.did = d.id;
and do the query on that table, everything works fine with correct sum. Is there a way to do this in the original query, with a subquery or something like that?