5 min read

GROUP BY and HAVING in SQL, with examples

GROUP BY collapses rows that share a value into one row per group. Aggregate functions such as COUNT, SUM, AVG, MIN and MAX then summarise each group.

One row per group

How many customers are there in each country?

SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
ORDER BY customers DESC, country;

Every column in the SELECT list must either be in the GROUP BY or be inside an aggregate function.

WHERE filters rows, HAVING filters groups

WHERE runs before grouping, so it decides which rows go into the groups. HAVING runs after, so it can filter on the aggregate itself. This finds customers who placed more than five orders.

SELECT c.email, COUNT(*) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
HAVING COUNT(*) > 5
ORDER BY order_count DESC, c.email;

Showing 8 of 9 rows.

Using both together

Ignore cancelled orders with WHERE, then keep only months with more than 3,000 in revenue with HAVING.

SELECT strftime('%Y-%m', o.order_date) AS month,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY month
HAVING revenue > 3000
ORDER BY month;

Order of evaluation

SQL processes a query in this order: FROM and joins → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Knowing this explains why you can't use an aggregate in WHERE.