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

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

Given this table, “Westerosians”:

Name | GreatHouseAllegience |
—— | —— |
Arya | Stark |
Cercei | Lannister |
Myrcella | Lannister |
Yara | Greyjoy |
Catelyn | Stark |
Sansa | Stark |

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…

House | Number_of_Westerosians |
—— | —— |
Stark | 3 |
Greyjoy | 1 |
Lannister | 2 |

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…

House | Number_of_Westerosians |
—— | —— |
Stark | 3 |
Lannister | 2 |
Greyjoy | 1 |