SQL GROUP BY and HAVING: aggregate and filter groups
GROUP BY collapses rows that share a value into one row per group. Aggregates like COUNT, SUM, AVG, MIN and MAX then summarize each group.
One row per group
Every column you SELECT must either be in GROUP BY or be inside an aggregate. Postgres rejects anything else, because a group has many values for that column and it can't pick one.
SELECT country, COUNT(*) AS customers FROM customers GROUP BY country;
WHERE filters rows, HAVING filters groups
WHERE runs before grouping, so it can't see aggregates. HAVING runs after, so it's where conditions like COUNT(*) >= 2 go. Use both in one query when you need to.
SELECT customer_id, SUM(amount) AS total FROM orders WHERE status = 'completed' GROUP BY customer_id HAVING SUM(amount) > 200;
Conditional aggregates
COUNT(*) FILTER (WHERE ...) counts only some rows in each group, so you can compute several counts or a ratio in one pass.
Try it
This runs real PostgreSQL in your browser on the practice tables. Change it and run it again.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Common mistakes
- Putting COUNT() or SUM() in WHERE (use HAVING).
- Selecting a column that isn't grouped or aggregated.
- Using OR in HAVING when the question needs both conditions.
- Integer division in ratios: cast to numeric first.
Practice drills
Reading it isn't enough. Solve these and it'll still be there in an interview.
- Revenue per customerCOUNT · SUM · GROUP BYFree
- Revenue by countryJOIN + GROUP BYFree
- High-value repeat buyersGROUP BY · HAVINGFree
- Order count, including zeroLEFT JOIN · COUNT(col) vs COUNT(*)Free
- Duplicate card chargesGROUP BY · HAVING to find duplicatesPro
- Monthly spendto_char · GROUP BY monthPro
- Spend by category, one row per accountPivot with FILTER / CASEPro
- Signup-to-purchase funnelCOUNT(DISTINCT) · funnel stepsPro
- Median salary by departmentPERCENTILE_CONT · ordered-set aggregatesPro
- Refund rate per customerConditional aggregation · FILTERPro
- Active in every monthCOUNT(DISTINCT month) · relational divisionPro
- Pricing to checkout within an hourFunnel with a time window · range joinPro