Common HAVING Clause Patterns
You'll learn: The most frequently used HAVING clause patterns that solve real-world data analysis problems.
After understanding how HAVING works, it's time to recognize the patterns you'll use again and again in practice. These patterns help you answer typical business questions by filtering grouped data based on aggregate conditions.
Finding Top Performers
One common pattern identifies groups that exceed a threshold. For example, finding customers who spent more than $1000 total, or products with more than 50 sales:
SELECT customer_id, SUM(order_amount) as total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) > 1000
ORDER BY total_spent DESC;
This pattern answers "Who are our high-value customers?"
Filtering by Activity Thresholds
Another pattern finds groups with sufficient activity to be statistically meaningful:
SELECT product_category, AVG(rating) as avg_rating
FROM reviews
GROUP BY product_category
HAVING COUNT(*) >= 10;
This ensures you only see categories with at least 10 reviews—avoiding misleading averages from tiny samples.
Identifying Outliers
You can spot unusual groups by combining multiple conditions:
SELECT store_id, COUNT(*) as sales_count, AVG(sale_amount) as avg_sale
FROM sales
GROUP BY store_id
HAVING COUNT(*) > 100 AND AVG(sale_amount) < 20;
This finds stores with high transaction volume but low average sales—potential red flags or opportunities.
Finding Empty or Complete Groups
Use HAVING to find groups missing expected data:
SELECT employee_id
FROM tasks
GROUP BY employee_id
HAVING COUNT(*) = 0; -- No tasks assigned
Key Takeaway: Common HAVING patterns—filtering by aggregate thresholds, ensuring minimum sample sizes, and identifying outliers—turn grouped data into actionable business insights by focusing on groups that meet specific criteria.