GROUP BY & HAVING

The previous lesson's aggregate functions always summarized the entire result set into a single row - one average, one count, covering every review in the table at once. GROUP BY changes that: it splits rows into buckets first, based on the values in one or more columns, and then runs each aggregate function once per bucket instead of once overall. That's the difference between "the average rating across all of StreamHub" and "the average rating for every movie, all at once."

One Aggregate Result Per Group

Instead of running a separate query for every movie's average rating, GROUP BY movie_id gives you all of them in a single pass:

SELECT movie_id, AVG(rating) AS avg_rating, COUNT(*) AS review_count
FROM reviews
GROUP BY movie_id;

MySQL grouped every review row by its movie_id, then computed AVG(rating) and COUNT(*) separately within each group. One query, six summary rows - one for every movie that has at least one review.

Grouping by More Than One Column

GROUP BY isn't limited to a single column. Grouping by both movie_id and the year a review was written creates a bucket for every movie/year combination - YEAR(review_date) pulls the year out of a date, one of several date functions the upcoming Date & Time Functions lesson covers in depth:

SELECT movie_id, YEAR(review_date) AS review_year, COUNT(*) AS review_count
FROM reviews
GROUP BY movie_id, YEAR(review_date);

Movie 1 only has one row here because both of its reviews happen to fall in 2023 - if it had reviews spanning two different years, it would get two separate rows, one per year.

The Rule: Every SELECT Column Must Be Grouped or Aggregated

MySQL enforces a strict rule on any query that uses GROUP BY: every column named in the SELECT list must either appear in the GROUP BY clause, or be wrapped inside an aggregate function like AVG() or COUNT(). This isn't an arbitrary restriction - it's a direct consequence of what grouping actually does. If reviews are being collapsed down to one row per movie_id, and a single movie has three reviews with three different rating values, MySQL has no way to decide which single rating to display for that group unless you tell it how to summarize those three values (with AVG(), MIN(), and so on). Trying to select a bare, non-aggregated, non-grouped column is a genuine SQL error:

SELECT movie_id, rating, COUNT(*)
FROM reviews
GROUP BY movie_id;

HAVING: Filtering After Grouping

WHERE filters individual rows before grouping happens, so it can never reference an aggregate result - the aggregate doesn't exist yet at that point in the query. HAVING is the equivalent filter that runs after grouping and aggregation, so it can test the aggregate values themselves. Movies with a genuinely good average rating - say, 4 or higher:

SELECT movie_id, AVG(rating) AS avg_rating
FROM reviews
GROUP BY movie_id
HAVING avg_rating >= 4;

Combining WHERE, GROUP BY, and HAVING

These three work together constantly. Say we only want to consider recent activity - reviews from the last year - and only see movies that picked up more than 5 such reviews:

SELECT movie_id, COUNT(*) AS recent_review_count
FROM reviews
WHERE review_date >= '2023-08-16'
GROUP BY movie_id
HAVING recent_review_count > 5;

Read left to right: WHERE throws out any review older than the cutoff date before grouping even starts. GROUP BY movie_id then buckets whatever survived into one group per movie. Finally HAVING throws out any group whose count didn't clear 5. Each clause does a genuinely different job, and none of them could do another's job in its place.

The Correct Clause Order

Getting the clause order wrong isn't just a style problem - it's a syntax error. Here's the full order MySQL expects, as a reference you'll come back to constantly:

SELECT movie_id, AVG(rating) AS avg_rating
FROM reviews
WHERE rating IS NOT NULL
GROUP BY movie_id
HAVING avg_rating >= 4
ORDER BY avg_rating DESC
LIMIT 5;

Writing HAVING before GROUP BY, or GROUP BY before WHERE, isn't just unconventional - MySQL simply won't parse it. Keeping this order memorized saves you from a surprising number of avoidable syntax errors.