Aggregate functions such as COUNT() generally cannot be used in WHERE.

✅ Correct:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

🔹 Real-World Data Science Example

Suppose you're analyzing customer transactions.

You want customers who have made at least 10 transactions:

SELECT customer_id,
       COUNT(*) AS transaction_count
FROM transactions
GROUP BY customer_id
HAVING COUNT(*) >= 10;

This can help identify:

• Frequent customers

• High-engagement users

• Active accounts

• Customers suitable for segmentation

Another example:

Find products generating more than 50000 in revenue:

SELECT product_id,
       SUM(quantity * price) AS revenue
FROM sales
GROUP BY product_id
HAVING SUM(quantity * price) > 50000
ORDER BY revenue DESC;

🔹 Interview Questions

What is HAVING?

HAVING filters groups created by GROUP BY.


What is the difference between WHERE and HAVING?

WHERE filters rows before grouping.

HAVING filters groups after grouping.


Can HAVING be used with aggregate functions?

Yes.

HAVING COUNT(*) > 5


Can WHERE and HAVING be used together?

Yes.

WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5

Why can't we normally use COUNT() in WHERE?

Because WHERE operates on individual rows before the aggregation is performed. HAVING is designed to filter aggregated groups.

🎯 Practice Questions

1. Find departments with more than 10 employees.

2. Find customers who have made at least 5 transactions.

3. Find regions where total sales exceed 100000.

4. Find departments where average salary is greater than 75000.

5. Find products whose total revenue exceeds 50000 and sort them from highest to lowest revenue.

🎯 Key Takeaways

• WHERE → Filters rows.

• GROUP BY → Creates groups.

• HAVING → Filters groups.

• WHERE is applied before grouping.

• HAVING is applied after grouping.

• HAVING is commonly used with COUNT(), SUM(), AVG(), MIN(), and MAX().

• WHERE + GROUP BY + HAVING is one of the most important SQL patterns for analytics.

📍 Double Tap ❤️ For More