0
Challenge · 12SQL I: Asking Questions of Data
GROUP BY & HAVING
SQLused in ~85% of apps
Every query you've written so far returns one row per post. DevLog's profile page needs a different shape entirely: one row per AUTHOR — their post count, their total likes. That is a query asking a question about GROUPS of rows, and it is where a beginner writes their first `column must appear in the GROUP BY clause` error and, right after fixing it, tries to filter those groups with the clause they already know — WHERE — and gets a second error for free.
Core idea. GROUP BY collapses every row that shares a value into one group and computes an aggregate per group — and every column you SELECT must be either the thing you grouped by or wrapped in an aggregate, no third option. HAVING then filters those already-collapsed groups by an aggregate condition; WHERE cannot do this, because WHERE runs before the groups exist.
poke it, drag it,
try to break it
try to break it
no slides here.
you drive.
you drive.
Playground, this isn't a screenshot
posts
alicelike_count = 8
alicelike_count = 0
boblike_count = 3
Three separate rows. No aggregate exists yet — there is nothing here for a HAVING clause to test.
Three rows, no grouping applied yet — this is what FROM produces.
The rule:
GROUP BY runs before there is any aggregate to test. HAVING is the only clause that runs after the collapse, which is why it is the only clause allowed to filter on count(*), sum(...), or any other aggregate.↑ play a little first, then dig in