USE GROUP BY to COUNT the number of rows for each unique entry in a given column

Download sql eBook

Example

Let's say you want to generate counts or subtotals for a given value in a column.

Given this table, "Westerosians":

NameGreatHouseAllegience
AryaStark
CerceiLannister
MyrcellaLannister
YaraGreyjoy
CatelynStark
SansaStark

Without GROUP BY, COUNT will simply return a total number of rows:

SELECT Count(*) Number_of_Westerosians
FROM Westerosians

returns...

Number_of_Westerosians
6

But by adding GROUP BY, we can COUNT the users for each value in a given column, to return the number of people in a given Great House, say:

SELECT GreatHouseAllegience House, Count(*) Number_of_Westerosians
FROM Westerosians
GROUP BY GreatHouseAllegience

returns...

HouseNumber_of_Westerosians
Stark3
Greyjoy1
Lannister2

It's common to combine GROUP BY with ORDER BY to sort results by largest or smallest category:

SELECT GreatHouseAllegience House, Count(*) Number_of_Westerosians
FROM Westerosians
GROUP BY GreatHouseAllegience
ORDER BY Number_of_Westerosians Desc

returns...

HouseNumber_of_Westerosians
Stark3
Lannister2
Greyjoy1

Stats

Contributors: 3
2016-07-21
Licensed under: CC-BY-SA

Not affiliated with Stack Overflow
Rip Tutorial: info@zzzprojects.com

Download eBook