Filtering Groups with HAVING in SQL

Table of Contents

1. Introduction
2. What is the HAVING Clause?
3. Difference Between WHERE and HAVING
4. Why Use HAVING?
5. Basic Syntax of HAVING
6. Simple Example with HAVING
7. Using HAVING with COUNT()
8. Filtering on SUM() with HAVING
9. Using HAVING with AVG(), MIN(), MAX()
10. Combining WHERE and HAVING
11. HAVING with Multiple Conditions
12. Sorting After HAVING
13. HAVING Without GROUP BY (Yes, You Can!)
14. Real-World Use Case: Active Customers
15. Real-World Use Case: Sales by Region
16. Common Errors with HAVING
17. Performance Considerations
18. Best Practices for Using HAVING
19. Summary Table: HAVING vs WHERE
20. Summary and What’s Next

1. Introduction

The HAVING clause in SQL allows you to filter grouped data after aggregation. It’s often misunderstood or confused with WHERE, but they serve very different purposes. In this module, we’ll clarify the role of HAVING and teach you how to use it effectively.

2. What is the HAVING Clause?

HAVING is used to filter the results of GROUP BY queries based on aggregate functions like COUNT(), SUM(), AVG(), etc.

3. Difference Between WHERE and HAVING

4. Why Use HAVING?

HAVING lets you:

• Show only groups that meet certain conditions
• Filter aggregated data
• Extend the power of GROUP BY

5. Basic Syntax of HAVING

sqlCopyEditSELECT column, AGG_FUNC(column)
FROM table
GROUP BY column
HAVING AGG_FUNC(column) condition;

Example:

sqlCopyEditSELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department
HAVING total_employees > 5;

6. Simple Example with HAVING

sqlCopyEditSELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING user_count >= 10;

This returns only cities with 10 or more users.

7. Using HAVING with COUNT()

sqlCopyEditSELECT product_id, COUNT(*) AS order_count
FROM orders
GROUP BY product_id
HAVING order_count > 20;

This helps identify popular products.

8. Filtering on SUM() with HAVING

sqlCopyEditSELECT customer_id, SUM(amount) AS total_spent
FROM transactions
GROUP BY customer_id
HAVING total_spent > 1000;

Finds customers who’ve spent over ₹1000.

9. Using HAVING with AVG(), MIN(), MAX()

sqlCopyEditSELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING avg_price < 500;

You can apply HAVING to any aggregate metric.

10. Combining WHERE and HAVING

sqlCopyEditSELECT department, COUNT(*) AS total
FROM employees
WHERE is_active = TRUE
GROUP BY department
HAVING total >= 5;

• WHERE filters before grouping
• HAVING filters after aggregation

11. HAVING with Multiple Conditions

sqlCopyEditSELECT region, SUM(sales) AS total_sales
FROM orders
GROUP BY region
HAVING total_sales > 100000 AND COUNT(*) > 10;

You can use AND, OR, and even parentheses for logic.

12. Sorting After HAVING

sqlCopyEditSELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 40000
ORDER BY avg_salary DESC;

ORDER BY works after HAVING in the execution order.

13. HAVING Without GROUP BY (Yes, You Can!)

Yes, HAVING can be used without GROUP BY — usually to filter on aggregates over the entire table.

sqlCopyEditSELECT SUM(amount) AS total_revenue
FROM sales
HAVING total_revenue > 100000;

14. Real-World Use Case: Active Customers

sqlCopyEditSELECT user_id, COUNT(*) AS login_count
FROM logins
GROUP BY user_id
HAVING login_count >= 20;

Find power users based on login activity.

15. Real-World Use Case: Sales by Region

sqlCopyEditSELECT region, SUM(total_amount) AS total_sales
FROM orders
GROUP BY region
HAVING total_sales > 50000;

Filter out underperforming regions.

16. Common Errors with HAVING

17. Performance Considerations

• Use WHERE for row-level filters to reduce rows before grouping
• Avoid putting non-aggregated conditions in HAVING
• Combine HAVING with indexes for faster filtering

18. Best Practices for Using HAVING

• Only use HAVING for aggregated conditions
• Use meaningful aliases for readability
• Use WHERE first to limit rows
• Document logic when combining with GROUP BY

19. Summary Table: HAVING vs WHERE

20. Summary and What’s Next

The HAVING clause is your post-aggregation filter, allowing you to include only those groups that meet a specific condition. Combined with GROUP BY and aggregate functions, it becomes a powerful tool for analytical queries.