🔹 HAVING Clause in SQL
Definition:
The HAVING clause is used to filter grouped records created by GROUP BY. Unlike WHERE which filters rows **before** grouping, HAVING filters **after** the grouping is done. It's often used with aggregate functions like COUNT(), SUM(), AVG(), etc.
Syntax:
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;
Example:
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 3;
Sample employees Table:
| id | name | department |
|---|
| 1 | Alice | HR |
| 2 | Bob | IT |
| 3 | Charlie | IT |
| 4 | Daisy | HR |
| 5 | Eva | IT |
| 6 | Frank | HR |
| 7 | Grace | Sales |
| 8 | Henry | HR |
Output:
| department | employee_count |
|---|
| HR | 4 |
Explanation:
The query groups employees by their department using GROUP BY. Then, using the HAVING COUNT(*) > 3 clause, it filters only those departments where the number of employees is greater than 3. In our table, only the HR department has 4 employees, so it's the only one shown in the output.
Usage Tips:
- Use
HAVING only with GROUP BY or aggregate functions.
- Use
WHERE to filter rows before grouping and HAVING to filter after.
Common Mistake:
Trying to use HAVING without GROUP BY or an aggregate function:
❌ SELECT name FROM employees HAVING department = 'IT';
✅ Use WHERE for such row-level conditions.
📘 Note: HAVING is essential when filtering results based on aggregates like total salary, average age, or count of items.