This site runs best with JavaScript enabled.Lesson 21: Aggregate functions using the GROUP BY clause.

Lesson 21: Aggregate functions using the GROUP BY clause.


Stepping up our function powers.

Using your new-found SQL function (COUNT, SUM, AVG, MIN, MAX) superpowers on the whole data table won't happen as often as you might hope. It's more useful to our business area if we are able to split our data up into smaller segments to allow greater comparison across these very, very useful aggregated functions.

So we need a way to group togther certain data points to allow us to perform our functions and get a more useable result. And we do that with the GROUP BY clause.

Let's see an example.

In our Footballer example, it would be useful to see how many players we had in our table. It doesn’t really tell us if certain clubs are higher payers overall or if the top earning players are clustered at certain clubs.

PlayerIDPlayerNameTeamPlayerWage
1MessiBARCELONA500000
2RonaldoJUVENTUS450000
3NeymarPSG400000
4SuarezBARCELONA350000
5KaneTOTTENHAM200000

We will re-do our COUNT function next but bring in another variable (the column TEAM) to see if one club has more high earners than the others. Our SQL looks like this:

SELECT Team, COUNT(PlayerID)
FROM Players
GROUP BY TEAM;

And our SQL results would look like this:

TEAMNumberOfPlayers
BARCELONA2
JUVENTUS1
PSG1
TOTTENHAM1

A quick scan of our results shows that Barcelona have more high earning players than any other team in our example data table.

So how did we set that up?

From the SQL, you can see we asked for two elements in our SELECT statement:

1) Team 2) A COUNT of the PlayerID rows

We wanted to query the PLAYERS table so that went in the FROM line.

And we used the GROUP BY clause at the end to specify that we wanted to group our results by the Team column to segment our results.

Quiz Time.

The Question.

Just knowing how many superheroes we have in total is good. Knowing which different nationalities they represent is good.

But we want to know how many superheroes of each nationality we have.

So we'll be using both COUNT and GROUP BY. Give it a go and see how you get on.

The Data.

The Editor.



The Answer.

Correct SQL:

Correct Output:

Share lesson