GROUP BY & HAVING

The last lesson's aggregates summarized an entire table into one row. GROUP BY lets you summarize it into many rows instead — one per distinct value (or combination of values) in the columns you group by. HAVING then filters those grouped rows, the same way WHERE filters ungrouped ones. We'll group RecipeBox's reviews by recipe to see per-recipe statistics.

GROUP BY Basics

When a SELECT has both a plain column and an aggregate function, GROUP BY tells SQLite how to bucket the rows before the aggregate runs — one aggregate result per bucket.

-- Average rating and review count, per recipe
SELECT r.title, AVG(rv.rating) AS avg_rating, COUNT(rv.id) AS review_count
FROM recipes r
JOIN reviews rv ON rv.recipe_id = r.id
GROUP BY r.id, r.title;

Every non-aggregated column in the SELECT list should appear in GROUP BY — here that's r.id and r.title. SQLite is more lenient about this than some databases (it won't always error if you omit one), but relying on that leniency produces results that are technically ambiguous, so it's best avoided.

Grouping by Multiple Columns

-- How many recipes exist per user, per category
SELECT user_id, category, COUNT(*) AS recipe_count
FROM recipes
GROUP BY user_id, category
ORDER BY user_id, recipe_count DESC;

HAVING: Filtering Groups

WHERE filters individual rows before grouping happens; HAVING filters the grouped results after aggregation. That's the whole distinction — and it's why HAVING can reference aggregate functions like COUNT(*) or AVG(...) while WHERE cannot (an aggregate value doesn't exist yet at the point WHERE runs).

-- Only recipes that have more than 5 reviews
SELECT r.title, COUNT(rv.id) AS review_count
FROM recipes r
JOIN reviews rv ON rv.recipe_id = r.id
GROUP BY r.id, r.title
HAVING COUNT(rv.id) > 5;

Combining WHERE and HAVING

A single query can use both: WHERE narrows down the rows that get grouped in the first place, and HAVING then narrows down which resulting groups make it into the output.

-- Among only 2024's reviews, recipes averaging 4+ stars with at least 3 reviews
SELECT r.title, AVG(rv.rating) AS avg_rating, COUNT(rv.id) AS review_count
FROM recipes r
JOIN reviews rv ON rv.recipe_id = r.id
WHERE rv.created_at >= '2024-01-01'
GROUP BY r.id, r.title
HAVING AVG(rv.rating) >= 4 AND COUNT(rv.id) >= 3
ORDER BY avg_rating DESC;