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

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

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


Got any SQL Question?