Difference between revisions of "The GROUPing pitfall"
(Version 0.1) |
(Version 0.11) |
||
| Line 64: | Line 64: | ||
I'm sure you understand what happens when they are joined together: | I'm sure you understand what happens when they are joined together: | ||
{{MySQL|query=SELECT monkey.value AS monkey_name, poop.size AS poop_size | {{MySQL|query=SELECT monkey.value AS monkey_name, poop.size AS poop_size | ||
FROM monkey | FROM monkey | ||
JOIN poop ON poop.monkeyid = monkey.id}} | JOIN poop ON poop.monkeyid = monkey.id}} | ||
| Line 71: | Line 70: | ||
{| border = 1 | {| border = 1 | ||
|+ Query results | |||
! monkey_name | ! monkey_name | ||
! poop_size | ! poop_size | ||
| Line 88: | Line 88: | ||
But you already knew that. I mean, that's kid stuff! I didn't even write it out, I actually got one of my younger siblings to type it out because I was so bored by it. | But you already knew that. I mean, that's kid stuff! I didn't even write it out, I actually got one of my younger siblings to type it out because I was so bored by it. | ||
You see that Bimbo shows up twice - the one row in the monkeys table got multiplied by all the matching rows in the poop table that it got joined to. You've seen it before, and if you were going to use this data in a report that displayed one record per monkey, you would know what to do - use a GROUP BY! | |||
{{MySQL|query=SELECT monkey.value AS monkey_name, AVG(poop.size) AS average_poop_size, SUM(poop.size) total_poop, COUNT(poop.id) AS number_of_poops | |||
FROM monkey | |||
JOIN poop ON poop.monkeyid = monkey.id | |||
GROUP BY monkey.id}} | |||
Revision as of 19:53, 19 January 2010
The GROUPing pitfall is a very common logical error in SQL queries, especially among people getting familiar with a DBMS.
This is what will happen - you'll be writing a query with a couple JOINs in it. You add a GROUP BY for the correct columns, and everything looks peachy. Then, at some point in the future (could be 5 minutes, could be a month), BOOM! Your query is returning random shit, and you have no clue why.
Considering that we here at ISoft pay the bills by writing queries that DON'T return random shit, this is something you should probably care about.
The short version
If your query joins on more than one table with a one-to-many relationship with the row you're grouping by, you CAN NOT use any aggregate functions in your query.
If you do, then any time that more than one of those JOINs matches more than one row, all of your aggregate functions will break.
Wait, why?
You know about JOINs, right? Let's say you have two tables:
| id (primary key) | value | weight |
|---|---|---|
| 1 | Bobo | 220 |
| 2 | Bimbo | 200 |
| 3 | The Hooker | 375 |
| id (primary key) | monkeyid | date | size |
|---|---|---|---|
| 1 | 2 | 2010-01-01 13:43:04 | 4.3 |
| 2 | 3 | 2010-01-02 06:12:44 | 3.8 |
| 3 | 1 | 2010-01-02 09:14:56 | 4.0 |
| 3 | 2 | 2010-01-02 15:05:33 | 2.6 |
As you can infer, there is a one-to-many relationship between "monkey" and "poop". That is to say, one monkey can have any number of poops.
I'm sure you understand what happens when they are joined together: Template:MySQL
You get this!
| monkey_name | poop_size |
|---|---|
| Bobo | 4.0 |
| Bimbo | 4.3 |
| Bimbo | 2.6 |
| The Hooker | 3.8 |
But you already knew that. I mean, that's kid stuff! I didn't even write it out, I actually got one of my younger siblings to type it out because I was so bored by it.
You see that Bimbo shows up twice - the one row in the monkeys table got multiplied by all the matching rows in the poop table that it got joined to. You've seen it before, and if you were going to use this data in a report that displayed one record per monkey, you would know what to do - use a GROUP BY!
