Aggregation
GROUP BY, HAVING, and the reduction of many rows to a statistic.
Level: beginner
What is it?
SQL aggregation reduces multiple rows to summary values using functions such as COUNT, SUM, AVG, MIN, and MAX. GROUP BY forms groups based on selected expressions, and HAVING filters those groups after aggregate values are computed.
Why does it matter?
Aggregations answer reporting and analytical questions without requiring the application to fetch every row and compute totals itself. Correct grouping and join semantics are essential to avoid counting duplicated rows.
Mental model
WHERE filters input rows first; GROUP BY partitions the remaining rows; aggregate functions reduce each group; HAVING filters the groups; SELECT returns expressions allowed by the query's grouping rules.
How it works
- COUNT(*) counts rows, while COUNT(column) excludes rows where that column is NULL.
- WHERE filters individual rows before grouping; HAVING filters aggregate groups.
- GROUP BY on several columns creates a group for each distinct combination under the DBMS's comparison rules.
- Joins can multiply rows before aggregation, creating inflated sums/counts if the intended grain is not preserved.
- Indexes, statistics, and hash/sort aggregation choices affect execution cost.
Steps
- Decide the result's grain, such as one row per customer or per month.
- Join only the data needed and verify the join does not unintentionally multiply facts.
- Filter individual rows with WHERE and form groups with GROUP BY.
- Use aggregate expressions and HAVING to filter groups, then check counts against a small hand-calculated example.
Real-world usage
- Calculating order totals, monthly active users, average scores, and inventory summaries.
- Building dashboards and reports over relational data.
Common mistakes
- Using WHERE to test an aggregate result when HAVING is required.
- Confusing COUNT(*) with COUNT(nullable_column).
- Summing a parent amount after joining to multiple child rows and double-counting it.
Interview questions
What is the difference between WHERE and HAVING?
WHERE filters input rows before grouping; HAVING filters groups based on aggregate or grouped expressions.
Why can a join inflate an aggregate?
A one-to-many join can repeat a fact row once for each matching child, so aggregate sums/counts may no longer represent the intended grain.