Topic 19 of 52 · Intermediate
Aggregates, GROUP BY & HAVING
Summarise data with COUNT, SUM, AVG, MIN and MAX.
Concept
Aggregate functions turn many rows into one value. GROUP BY splits rows into groups and computes the aggregate per group.
WHERE filters rows before grouping; HAVING filters groups after aggregation.
COUNT(*) counts rows, COUNT(col) counts non-NULL values and COUNT(DISTINCT col) counts unique values.
Code (SQL · MySQL 8)
SELECT c.city,
COUNT(*) AS orders,
SUM(o.amount) AS revenue,
ROUND(AVG(o.amount), 2) AS avg_order
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'delivered'
GROUP BY c.city
HAVING SUM(o.amount) > 10000
ORDER BY revenue DESC;Remember
- Every non-aggregated SELECT column must be in GROUP BY.
- WHERE = before grouping, HAVING = after.
- Aggregates ignore NULLs (except COUNT(*)).
Interview questions
WHERE vs HAVING?
WHERE filters individual rows and cannot use aggregates. HAVING filters groups and can use aggregates like SUM(amount) > 1000.
COUNT(*) vs COUNT(col)?
COUNT(*) counts all rows; COUNT(col) skips rows where col is NULL.
Lesson test
Take an 8-question test on “Aggregates, GROUP BY & HAVING” — concepts, output prediction, bug spotting and interview questions.