Getting SUM() on distinct rows in mysql?

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.

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.

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 Aug 27 '09 at 20:38 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 Aug 27 '09 at 20:40 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 Aug 27 '09 at 20:49 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 Aug 27 '09 at 20:52 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 Aug 27 '09 at 20:52.

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.

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 Aug 27 '09 at 19:58.

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.

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 Aug 27 '09 at 20:01 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 Aug 27 '09 at 20:20.

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.

Great to know, thanks. – Par Aug 27 '09 at 20:54.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.