GROUP BY: one row per group
Grouping collapses rows, and every column you select has to survive the collapse.
Collapsing rows into groups
GROUP BY city turns many rows per city into one row per city. Anything you select must then make sense for a whole group: either it is the thing you grouped by, or it is an aggregate such as COUNT, SUM, AVG, MIN or MAX.
Select a column that is neither and the question has no answer — "the name" of a group of forty students is not a thing. PostgreSQL refuses the query outright. MySQL historically returned an arbitrary row's value, which is worse than an error because the result looks fine.
WHERE filters rows, HAVING filters groups
WHERE runs before grouping and decides which rows take part. HAVING runs after and decides which groups survive. "Cities with more than ten students" is HAVING COUNT(*) > 10, because the count does not exist until the grouping has happened.
Use both when you mean both: WHERE year = 2026 first narrows the rows, then HAVING COUNT(*) > 10 narrows the groups. Doing the filtering in WHERE where you can is also the cheaper order — fewer rows reach the grouping.
The order clauses actually run in
A query is written SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY — and it runs FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. SELECT is almost last.
That explains two things that otherwise look arbitrary. An alias defined in SELECT cannot be used in WHERE, because WHERE ran before the alias existed. But it can be used in ORDER BY, which runs after. Knowing the running order turns both from rules to memorise into consequences.
Worked examples
Cities with more than ten students, busiest first
/SELECT city, COUNT(*) AS students FROM students GROUP BY city HAVING COUNT(*) > 10 ORDER BY students DESC/Against:
students(name, city, year)students is an alias from SELECT, and ORDER BY may use it because ORDER BY runs last.
Check yourself
Practice questions written for this lesson — not past exam papers.
Practise every question in this subject
Which filters groups rather than rows?
Why can an alias from SELECT be used in ORDER BY but not in WHERE?