StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POGetting SUM() on distinct rows in mysql
text
Body
copied!<p>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:</p> <pre>TABLE dump: id INT date DATE event VARCHAR(100) amount DECIMAL(10, 2)</pre> <pre>TABLE dump_cat: id INT did INT (id in dump) cid INT (id in categories)</pre> <pre>TABLE categories: id INT name VARCHAR(100)</pre> <p>Now the query I'm trying to use is:</p> <pre>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;</pre> <p>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:</p> <pre>+---------+--------------------------+-------+-----------+ | month | name | catid | sum | +---------+--------------------------+-------+-----------+ | 2008-08 | Cash | 21 | -6200.00 | | 2008-08 | Gas | 8 | -2936.19 | | 2008-08 | Rent | 1 | -15682.00 | </pre> <p>where as</p> <pre>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;</pre> <p>returns</p> <pre>+------+----------+ | id | amount | +------+----------+ | 3961 | -600.00 | | 2976 | -200.00 | | 2967 | -400.00 | | 2964 | -200.00 | | 2957 | -300.00 | | 2962 | -1400.00 | +------+----------+</pre> <p>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.</p> <p>Added: If I collect the dump and dump_cat tables into one, with </p> <pre>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;</pre> <p>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?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload