Basic · Lesson 6 of 14

GROUP BY and HAVING

Answers per category, the heart of analytics.

Split then summarize

GROUP BY turns aggregates into per-group answers. Every non-aggregate column in SELECT must appear in GROUP BY, otherwise the database cannot know which rows form a group.

HAVING filters groups

WHERE cannot see aggregates, so after grouping you filter with HAVING. "Customers with more than two orders" is a HAVING question.

Order of operations

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT. Rehearse this once and WHERE versus HAVING stops being confusing.

Examples

Count by category

Rows per country.

SELECT country, COUNT(*) AS user_count FROM users GROUP BY country;

Per group totals

Watch time by device type.

SELECT device, SUM(minutes_watched) AS minutes FROM watch_events GROUP BY device;

Filter groups with HAVING

A trivial example, but the syntax is what matters.

SELECT country, COUNT(*) AS n FROM users GROUP BY country HAVING COUNT(*) >= 1;