Using HAVING with Aggregate Functions
You'll learn how to filter grouped data based on calculated aggregate values like COUNT, SUM, AVG, MIN, and MAX.
The Core Idea
Now that you understand HAVING filters groups (not individual rows), let's see how it works with each aggregate function you've learned. HAVING lets you set conditions on the results of COUNT(), SUM(), AVG(), MIN(), and MAX() to keep only the groups that meet your criteria.
Think of it like grading group projects: you calculate each group's average score, then only announce the groups whose average is above 80.
Using HAVING with Each Aggregate
With COUNT:
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;
Shows only departments with more than 10 employees.
With SUM:
SELECT customer_id, SUM(order_amount) as total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) > 1000;
Finds customers who've spent over $1000 total.
With AVG:
SELECT product_category, AVG(price) as avg_price
FROM products
GROUP BY product_category
HAVING AVG(price) < 50;
Returns only categories with an average price under $50.
With MIN/MAX:
SELECT store_id, MAX(sale_amount) as largest_sale
FROM sales
GROUP BY store_id
HAVING MAX(sale_amount) > 5000;
Shows stores where the biggest single sale exceeded $5000.
Combining Multiple Conditions
You can use multiple aggregate conditions together:
SELECT region, COUNT(*) as num_sales, AVG(amount) as avg_amount
FROM sales
GROUP BY region
HAVING COUNT(*) >= 100 AND AVG(amount) > 500;
Key Takeaway: HAVING works with any aggregate function (COUNT, SUM, AVG, MIN, MAX) to filter groups based on their calculated values, letting you answer questions like "which groups are big enough?" or "which groups average above a threshold?"