Group Multiple Rows using GROUP BY

Sometimes you have to group together multiple rows in the result and then get an aggregate from the grouped rows - that's the function of GROUP BY.

Wait, you are thinking that useless, ain't you? You are wrong - that's why I am the teacher and you are the student. Muhahaha. Anyway, here's an example to prove you wrong(and me right)

Let say that you want to find the average IQ of the male and female population in the Simpson's universe. That can be done using this algorithm...

• First get the IQ of all female characters.
• Get the Average of that amount.
• Next, get the IQ of all male characters.
• Again, get the average.
• Finally show both the average values

That's a 5 step algorithm - watch me do that using a single SQL query. Go ahead - hit the execute button.

Understanding the Query

Let's break that query down. The first part of the query - "SELECT * FROM Character"(ignore the fields for now - we'll get to that later) will fetch all the rows in the table. When the 'GROUP BY sex' clause is added, SQL will take all the rows in the result with the same value for the 'sex' field and make it appear to be a single row. Yes, multiple rows will act like a single row.

That means that all the rows with the value 'm' in the sex field will be grouped together into one row and all the rows with 'f' as the sex will be grouped into another row. And since there is only 2 possible values for the sex field, there will be just two rows in the final result.

Fields

Now lets take a look at the fields we selected - sex and AVG(iq)...

sex

sex is the name of a field - since there are multiple 'rows' in each row of the result, if a field name is given, it will return the value of that field for the first row.

AVG(iq)

AVG() is an aggregate function. Aggregate functions can only be used if GROUP BY is used. AVG stands for Average(you must have guessed that by now). The argument to the AVG() function in the iq field. This function will look at the iq field's value in all the aggregated 'rows' in a row and get the average.

Some other aggregate functions are SUM(), MIN(), MAX(), COUNT() etc.

This illustration should make things clear. Please note that the value you get from the database may be different from the values given in the illustration.

Without GROUP BY			->	With GROUP BY
name	sex	iq		sex	iq
Homer Simpson	m	80		m	(80+100)/2 = 90
Bart Simpson	m	100
Marge Simpson	f	110		f	(110+159+100)/3 = 123
Lisa Simpson	f	159
Maggie Simpson	f	100

Now its your turn

I hope you understood the grouping mechanism. Try to find the SQL queries to do the following.

• Get the number of dump people(iq<100) and normal people(iq>=100). Hint: use the COUNT() function.
• Get the maximum IQ for male and female characters. That is Maximum Male IQ = ? and Maximum Female IQ = ?

Try out your queries here...

If you give up, the answers can be found in the last page.