Getting SUM() on distinct rows in mysql

Question

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?

Answer 1

At first examination it looks to me like you might have the Referential integrity constraint bgetween Dump and Dump_Cat backwards.

Can Transactions (in Dump) be in multiple categories? If not, then shouldn't the Transaction table, (Dump) specify which category each transaction is in, and not the otjher way around? i.e, should there be a CatId in the Dump table and not a DumpId in the Cat table?

if Transactions can be in Multiple categories, then your data structure is correct, butthen you will unavoidably be double (or multiply) counting transaction amounts in any aggregate query because the transaction amount is in fact in multiple categories.

Answer 2

If dump records can be in multiple categories, they will impact all of their category's rows for that month.

One solution for this is to also pull a COUNT() of categories for each dump record, and use that as a divisor for the individual amounts. Thus, the amount is apportioned automatically in an even way across all categories the dump record belongs to, preserving the integrity of the overall total.

Something like this (sorry, MySQL isn't my daily RDBMS, unsure of the exact syntax):

 SELECT SUBSTR(d.date,1,7) AS month, c.name, c.id AS catid, 
   SUM(d.amount / (SELECT COUNT(*) FROM dump_cat dc2 WHERE dc2.did=d.id)) 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;

Answer 3

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.

While you may have only one category per dump, you therefore must have multiple rows in dump_cat per dump. You should consider defining a UNIQUE constraint to ensure only one row exists per pair of did, cid:

ALTER TABLE dump_cat ADD CONSTRAINT UNIQUE (did, cid);

I predict this statement will fail given the current data in your table. It can't create a unique constraint when these columns already contain duplicates!

You can remove duplicates this way, for instance:

DELETE dc1 FROM dump_cat dc1 JOIN dump_cat dc2 USING (did, cid)
WHERE dc1.id > dc2.id; -- only delete the second duplicate entry

---

edit: By the way, don't mark my question accepted until you have verified that I'm correct! :-)

You can verify that there are in fact duplicates as I suggest by using a query like the following:

SELECT did, COUNT(*)
FROM dump_cat
GROUP BY did
HAVING COUNT(*) > 1;

Another possibility: you have more than one category with the same name? (sorry my first try at this query was wrong, here's an edited version)

SELECT c.name, GROUP_CONCAT(c.id) AS cat_id_list, COUNT(*) AS c
FROM category c
GROUP BY c.name
HAVING COUNT(*) > 1;

---

FWIW, I did test the DELETE command I showed:

INSERT INTO dump_cat (did, cid) VALUES (1, 2), (3,4), (3,4); -- duplicates!

DELETE dc1 FROM dump_cat dc1 JOIN dump_cat dc2 USING (did, cid) WHERE dc1.id > dc2.id
Query OK, 1 row affected (0.00 sec)

---

PS: This is tangential to your question, but the DISTINCT query modifier always applies to the whole row, not just the first column. This is a common misunderstanding of many SQL programmers.

Answer 4

You can take just about any query, like the one you used to create the distinct table, and just select off of that. Just give the the query a "table name".

SELECT SUBSTR(d_dc.date,1,7) AS month, c.name, c.id AS catid, SUM(d_dc.amount) AS sum
FROM (SELECT DISTINCT d.id, d.date, d.event, d.amount, dc.cid
    FROM dump AS d, dump_cat AS dc WHERE dc.did = d.id
    WHERE SUBSTR(d.date, 1, 7) >= '2008-08') AS d_dc
JOIN categories AS c ON d_dc.cid=c.id
GROUP BY month, c.name ORDER BY month

That's probably not the most efficient way to do your query, and I may have gotten some of the table aliases wrong, but that should give you an idea of how to do it.