← SQL roadmap

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.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Aggregates, GROUP BY & HAVING” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.