The GROUPing pitfall

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:

Monkey

id (primary key)	name	weight
1	Bobo	220
2	Bimbo	200
3	The Hooker	375

Poop

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
4	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:

SELECT monkey.name AS monkey_name, poop.size AS poop_size
FROM monkey
JOIN poop ON poop.monkeyid = monkey.id
ORDER BY monkey.id

You get this!

Query results

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 wanted to use this data in a report that displayed one record per monkey, you would know what to do - use a GROUP BY!

A completely valid query

SELECT monkey.name AS monkey_name, AVG(poop.size) AS average_poop_size, SUM(poop.size) total_poop, 
 COUNT(DISTINCT poop.id) AS number_of_poops 
FROM monkey
JOIN poop ON poop.monkeyid = monkey.id 
GROUP BY monkey.id

Query results

monkey_name	average_poop_size	total_poop	number_of_poops
Bobo	4.00	4.0	1
Bimbo	3.45	6.9	2
The Hooker	3.80	3.8	1

Boom, bitches. Fire up Crystal and turn that sucker into a report, amirite?

BUT WAIT!

Thanks to my narrative-writing powers, you remember another table that contains data relevant to this report!

Meal

id	monkeyid	description	weight	date
1	2	A delicious spaghetti dinner with a side of garlic bread.	1.5	2010-01-01 19:24:00
2	3	Shoots	4.7	2010-01-01 08:45:55
3	1	Leaves	3.4	2010-01-01 13:17:00
4	3	Monkey food	5.1	2010-01-01 17:43:19

Dude, the eating habits of these monkeys is totally the sort of thing you would want to use in your report on pooping frequency. Let's join that shit into your query!

SELECT monkey.name AS monkey_name, AVG(poop.size) AS average_poop_size, SUM(poop.size) total_poop, 
 COUNT(DISTINCT poop.id) AS number_of_poops, COUNT(DISTINCT meal.id) AS number_of_meals
FROM monkey
JOIN poop ON poop.monkeyid = monkey.id 
JOIN meal ON meal.monkeyid = monkey.id # This other table has useful data, too!
GROUP BY monkey.id

Query results

monkey_name	average_poop_size	total_poop	number_of_poops	number_of_meals
Bobo	4.00	4.0	1	1
Bimbo	3.45	6.9	2	1
The Hooker	3.80	7.6	1	2

Well aren't you a query-writing beast! Time to put this query into production code, eh?

WRONG!

If you were observant, you would have noticed something is amiss with those results - the total_poop of The Hooker is different than it was in the last query! What the junk?

Debugging time - let's run the query without GROUP BY (or the aggregate functions) and see what data we're working with:

SELECT monkey.name AS monkey_name, poop.size AS poop_size, poop.id AS poop_id, meal.id AS meal_id
FROM monkey
JOIN poop ON poop.monkeyid = monkey.id 
JOIN meal ON meal.monkeyid = monkey.id

Query results

monkey_name	poop_size	poop_id	meal_id
Bimbo	4.3	1	1
Bimbo	2.6	4	1
The Hooker	3.8	2	2
Bobo	4.0	3	3
The Hooker	3.8	2	4

Duh!

Of course! As you can see by the poop_id column, poop #2 is getting counted twice! And not only that, but meal #1 is also being counted twice - if we had used any aggregate functions on columns from the meal table, they would have returned fux0red data as well!

By drawing on your basic understanding of how JOINs work, you should quickly see what causes this. Every row that is related to a monkey id is going to be multiplied by the number of matching rows from tables are joined to that monkey id.

Of course, all those multiplied rows disappear from sight as soon as you GROUP BY some column - but the first aggregate function exposes the dirty truth: you didn't put any thought into how these tables related!

How to NOT make the mistake

For EVERY SINGLE JOIN you make, you must go through this thought process:

1. What is this query grouping by - what is the "primary key" of my result set?
2. This table/subquery I'm joining on - what is its relationship to that "primary key" (one-to-one, one-to-many)?
3. Does my (pre-GROUP) dataset already have a one-to-many relationship with my final result set?

If it does, then stop what you're doing - you're about to fuck up your results, and probably confuse yourself horribly in the process.

As someone who (I'm assuming) is going to be writing a lot of queries in the future (I don't see us moving to using flat-files as a storage engine any time soon), this is something that will need to become automatic for you.

Brain farts happen, but if you put effort into keeping track of which columns are unique on both sides of your joins, you should be able to make sense of what's going on in your results.