Course contentsShow
← Data Science
Lesson 904 of 2,14523. SQL Aggregation and GroupingFree lesson

Common HAVING Clause Patterns

Master typical use cases like finding top performers, filtering by thresholds, and identifying outliers.

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.