tags:

views:

696

answers:

4

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?

+1  A: 

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.

Charles Bretana
I am planning to use multiple categories, thus the database scheme with dump_cat taking care of the relations between dump and categories. But I haven't, yet, so the data I'm querying does only have one category per row in dump.
Par
+1  A: 

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;
richardtallent
I can see what you are after, but I haven't yet added any dump record to multiple categories. Every record in dump has only one category.
Par
Also, would that really affect the sum() grouped by category? I can only see that it would make the total sum, for the month, too big, but not the sum for each category since the dump records wouldn't be duplicated inside the category, right?
Par
+2  A: 

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.

Bill Karwin
Thanks a lot! I hadn't noticed this, there had been an error in the data import algorithm.Now, the delete duplicates sql doesn't work (Query OK, 0 rows affected), is there another way to write that?
Par
0 rows affected doesn't mean it didn't work, it means it didn't find any duplicates. So maybe my theory that you had duplicates is wrong.
Bill Karwin
I did verify it with "select did, cid, count(*) from dump_cat group by did, cid having count(*) > 1;". Still the delete didn't help. But I fixed it with "create table dump_cat_unique SELECT distinct * FROM dump_cat;" and then dropped the old dump_cat and renamed the new. Now everything is fine, thanks a lot again.
Par
Okay, I'm glad you found a solution. I don't know why the delete method didn't work for you. But all's well that ends well. :)
Bill Karwin
Oh, and thanks for the reminder about distinct applying to the whole row; I had forgotten that. Without it it would had been much harder to create the dump_cat_unique. :)
Par
+1  A: 

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.

Brent Baisley
Great to know, thanks.
Par