GROUP BY and HAVING

~14 min · sql, group-by, having

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Per-bucket summaries

GROUP BY partitions rows into buckets by one or more columns; aggregates then run per bucket. HAVING filters the buckets after aggregation (in contrast to WHERE, which filters rows before).

The mental order of operations:

FROM + joins assemble candidate rows.
WHERE filters those rows.
GROUP BY partitions surviving rows into buckets.
Aggregates compute one value per bucket.
HAVING filters buckets.
SELECT projects the columns/expressions you want.
ORDER BY + LIMIT finalize.

Warning: Postgres errors out if you GROUP BY a subset of selected columns. SQLite lets it through and picks an arbitrary value for the unaggregated columns — same trap as MySQL pre-5.7. Always include every non-aggregated SELECT column in GROUP BY, or wrap it in an aggregate.

Code

GROUP BY + HAVING·sql

-- Top 5 brains by message count this month
SELECT brain, count(*) AS n
FROM   messages
WHERE  created_at >= datetime('now', 'start of month')
GROUP  BY brain
HAVING count(*) > 100      -- ignore brains with low traffic
ORDER  BY n DESC
LIMIT  5;

Multi-column groups·sql

-- Per-day, per-brain message counts
SELECT date(created_at)        AS day,
       brain,
       count(*)                AS n
FROM   messages
GROUP  BY day, brain
ORDER  BY day, brain;

External links

Exercise

Build a small orders table (id, customer_id, total, created_at) with a few hundred rows. Write three queries: (1) per-customer order counts, (2) per-month revenue totals, (3) top-10 customers by lifetime spend, with a HAVING that excludes customers with fewer than 3 orders. Confirm by hand-spot-checking a few buckets.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousDISTINCT and Aggregate Functions Next →Subqueries — Inline Selects

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.