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

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.

Try out your queries here...

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

Previous
LIMIT Command 
Next
SQL Functions 
blog comments powered by Disqus
Subscribe to Feed