🌱BeginnerGrouping
HAVING
Filter groups after aggregation using HAVING the WHERE clause for GROUP BY.
Overview
HAVING filters groups produced by GROUP BY, the same way WHERE filters individual rows. The key distinction: WHERE runs before aggregation (filters rows), HAVING runs after aggregation (filters groups). You can use aggregate functions in HAVING but not in WHERE.
A common pattern: GROUP BY to compute aggregates per group, then HAVING to keep only groups meeting a threshold for example, customers who have placed more than 5 orders, or products with an average rating above 4.0.
Key Points
- •HAVING filters groups (runs after GROUP BY)
- •WHERE filters rows (runs before GROUP BY)
- •Aggregate functions can only be used in HAVING, not WHERE
- •HAVING without GROUP BY is valid applies to the entire result set as one group
- •Clause order: GROUP BY → HAVING → ORDER BY
Examples
Filter groups by aggregate threshold
-- Only categories with more than 5 products
SELECT category, COUNT(*) AS cnt
FROM products
GROUP BY category
HAVING COUNT(*) > 5;
-- Customers who spent over $1000 total
SELECT customer_id, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 1000
ORDER BY total_spent DESC;WHERE vs HAVING
-- WHERE filters before grouping (excludes cancelled orders from counts)
-- HAVING filters after grouping (keeps only high-value customers)
SELECT customer_id, SUM(total) AS total_spent
FROM orders
WHERE status <> 'cancelled' -- row-level filter
GROUP BY customer_id
HAVING SUM(total) > 500 -- group-level filter
ORDER BY total_spent DESC;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4