Member-only story
Stop Guessing at WHERE vs HAVING: One Idea Makes GROUP BY Click
Stop memorizing rules about WHERE vs HAVING. Build the mental model of how SQL groups, aggregates, and filters — then GROUPING SETS, ROLLUP, FILTER, and DISTINCT aggregates all fall into place. Runnable PostgreSQL throughout.
The One Idea That Explains Everything
If you’ve ever added a column to a SELECT and gotten the dreaded "must appear in the GROUP BY clause" error, you've been memorizing rules instead of understanding one idea. Here it is: GROUP BY does exactly one thing — it collapses many rows into one row per group. Every confusing part of aggregation, from WHERE vs HAVING to why COUNT(*) and COUNT(col) disagree, falls out of that single fact once you actually see it.
After grouping, each group is a single output row. So the only things you can put in the SELECT list are: (a) the columns you grouped by (constant within the group), and (b) aggregate functions that reduce the group's many values to one. Ask "does this produce one value per group?" and every rule answers itself.
PostgreSQL syntax throughout; MySQL differences noted.
