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 |