Collapsing Rows Into Groups
GROUP BY takes rows that share the same value in the grouping columns and collapses them into one row per group. Aggregate functions like COUNT, SUM, and AVG then compute a value for each group.
The Select List Rule
Every column in SELECT must either appear in GROUP BY or be wrapped in an aggregate. A bare non grouped column has no single value per group, so most engines reject it.
WHERE vs HAVING
These two filters run at different stages:
- WHERE filters individual rows before grouping.
- HAVING filters whole groups after the aggregate is computed.
- Use WHERE to drop rows cheaply, and HAVING only for conditions on aggregates.
A Common Example
To find departments with more than ten employees, you group by department, count rows, and add HAVING count greater than ten. The same count condition cannot go in WHERE because the count does not exist yet at that stage.
Key idea
GROUP BY collapses rows into groups for aggregation, WHERE filters rows before grouping, and HAVING filters groups using aggregate conditions.