Example
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 |
