Bug #31899 | Group functions not working properly when joining tables | ||
---|---|---|---|
Submitted: | 28 Oct 2007 19:55 | Modified: | 29 Oct 2007 11:19 |
Reporter: | Svavar Kjarrval | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
Version: | 5.0.45 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | GROUP, join |
[28 Oct 2007 19:55]
Svavar Kjarrval
[29 Oct 2007 4:19]
Giuseppe Maxia
Thank you for taking the time to write to us, but this is not a bug. You are joining the tables using a non unique key. Thus you have duplicates. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php
[29 Oct 2007 11:19]
Svavar Kjarrval
This not-a-bug can be solved the following way (for those who suffer from the same problem): This command produces the correct results for all test tables mentined in the bug report. However, it creates some temporary tables so it might not be the most efficient way to do it. SELECT T1.id, T1.name, SUM( T2.number ) AS results, result2, result3 FROM test1 AS T1 INNER JOIN test2 AS T2 ON T2.test1_id = T1.id INNER JOIN (SELECT SUM( T3.number ) AS result2 FROM test3 AS T3 WHERE T3.test1_id = '1' GROUP BY T3.test1_id) AS T3 INNER JOIN (SELECT SUM(T4.number ) AS result3 FROM test4 AS T4 WHERE T4.test1_id = '1' GROUP BY T4.test1_id) AS T4 WHERE T1.id = '1' GROUP BY T1.id