Previous
LIMIT Command
Next
SQL Functions
SQL(Structured Query Language) Tutorial
Group Multiple Rows using GROUP BY

# 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.

SELECT sex,AVG(iq) FROM Character GROUP BY sex

## 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
namesexiqsexiq
Homer Simpsonm80m(80+100)/2 = 90
Bart Simpsonm100
Marge Simpsonf110f(110+159+100)/3 = 123
Lisa Simpsonf159
Maggie Simpsonf100

## 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.

Previous
LIMIT Command
Next
SQL Functions