22. GROUP BY and HAVING

From "one aggregate for the whole table" to "one aggregate per group"

The previous lesson's aggregates collapsed the entire table into a single row. GROUP BY changes the unit of collapse: instead of one group (the whole table), you specify a column (or columns) whose distinct values each become their own group, and every aggregate in the SELECT list computes separately per group:

SELECT rating, count(*) AS film_count, avg(rental_rate) AS avg_rate
FROM film
GROUP BY rating;

Read this as: "partition all 1000 film rows into buckets by rating (5 buckets: G, PG, PG-13, R, NC-17), then compute count(*) and avg(rental_rate) independently within each bucket." One output row per distinct rating value.

The rule that governs the SELECT list

Once GROUP BY is present, every column in SELECT must be either part of the GROUP BY list, or wrapped in an aggregate. This isn't an arbitrary restriction — it's the same logical constraint from the previous lesson (SELECT title, avg(...) without GROUP BY failed for exactly this reason), just now scoped per-group instead of over the whole table:

-- Valid: rating is the GROUP BY column, count/avg are aggregates.
SELECT rating, count(*), avg(rental_rate) FROM film GROUP BY rating;

-- Invalid: title is neither grouped nor aggregated — which title would
-- represent an entire rating bucket of ~200 films? Undefined, so Postgres rejects it.
-- SELECT rating, title, count(*) FROM film GROUP BY rating;
-- ERROR:  column "film.title" must appear in the GROUP BY clause or be
-- used in an aggregate function

Grouping by multiple columns

GROUP BY a, b creates one group per distinct combination of (a, b) — same "combination, not independent" logic as DISTINCT from Module 1 (and that's not a coincidence: SELECT DISTINCT a, b is essentially GROUP BY a, b with no aggregates computed):

SELECT rating, rental_duration, count(*) AS film_count
FROM film
GROUP BY rating, rental_duration
ORDER BY rating, rental_duration;

HAVING vs WHERE — two different filters

Recall the logical evaluation order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE filters individual rows before grouping happens. HAVING filters whole groups after grouping and aggregation have already happened. This is why HAVING can reference aggregate functions and WHERE cannot — at the point WHERE runs, no aggregate value exists yet to filter on.

-- WHERE: filter rows BEFORE grouping — only PG/PG-13 films enter the
-- grouping step at all, so the counts below only reflect those.
SELECT rating, count(*) AS film_count
FROM film
WHERE rating IN ('PG', 'PG-13')
GROUP BY rating;

-- HAVING: group by everything, THEN keep only groups whose aggregate
-- satisfies a condition — ratings with more than 190 films, decided
-- AFTER all 5 groups' counts are already computed.
SELECT rating, count(*) AS film_count
FROM film
GROUP BY rating
HAVING count(*) > 190;

Both can appear in the same query, and often should — WHERE to cheaply discard irrelevant rows before the (more expensive) grouping work, and HAVING to filter on the results of that grouping:

-- Among films costing more than $2, which ratings have an average
-- rental_rate above $4.01?
SELECT rating, avg(rental_rate) AS avg_rate
FROM film
WHERE rental_rate > 2.00        -- row-level filter, applied first
GROUP BY rating
HAVING avg(rental_rate) > 4.01; -- group-level filter, applied after

A common beginner mistake worth naming directly: trying to write WHERE avg(rental_rate) > 4.01 to get this last result. It fails for the same reason WHERE COUNT(*) > 5 would fail — at the point WHERE runs, avg(rental_rate) hasn't been computed for any group yet, because groups don't exist yet either.

Check yourself

1. Once a query has GROUP BY rating, why can't you also SELECT title alongside rating and count(*)?
2. In your own words: what's the precise difference in when WHERE and HAVING each run?
3. Write a query that finds every rental_duration value with more than 200 films, using HAVING.