HAVING in MySQL

The HAVING clause in MySQL is used to filter grouped records after applying GROUP BY. It works only with aggregate functions like COUNT(), SUM(), AVG(), etc.

1. Difference Between WHERE and HAVING

2. Syntax

SELECT column_name, aggregate_function(column_name)FROM table_nameGROUP BY column_nameHAVING condition;

3. Example: Using HAVING with COUNT()

employees Table

Query: Show only departments with more than 1 employee

SELECT department, COUNT(*) AS total_employeesFROM employeesGROUP BY departmentHAVING total_employees > 1;

✅ Output:

+------------+----------------+| department | total_employees |+------------+----------------+| IT         | 2              || HR         | 2              |+------------+----------------+

🚀 Explanation:

• Groups employees by department.
• Uses HAVING COUNT(*) > 1 to filter only departments with more than 1 employee.

4. HAVING with SUM()

Query: Show departments where total salary is more than 100,000

SELECT department, SUM(salary) AS total_salaryFROM employeesGROUP BY departmentHAVING total_salary > 100000;

✅ Filters departments with a total salary above 100,000.

5. HAVING with AVG()

Query: Show departments with an average salary greater than 50,000

SELECT department, AVG(salary) AS avg_salaryFROM employeesGROUP BY departmentHAVING avg_salary > 50000;

✅ Filters departments where the average salary is more than 50,000.

6. HAVING with Multiple Conditions

You can use AND, OR, and other operators.

Query: Show departments with more than 1 employee AND total salary > 100,000

SELECT department, COUNT(*) AS total_employees, SUM(salary) AS total_salaryFROM employeesGROUP BY departmentHAVING total_employees > 1 AND total_salary > 100000;

✅ Filters departments that meet both conditions.

7. GROUP BY + WHERE + HAVING

You can combine WHERE and HAVING:

SELECT department, COUNT(*) AS total_employeesFROM employeesWHERE salary > 50000GROUP BY departmentHAVING total_employees > 1;

✅ Explanation:

• WHERE salary > 50000: Filters before grouping.
• GROUP BY department: Groups by department.
• HAVING total_employees > 1: Filters after grouping.

Key Takeaways

✅ Use WHERE for row-level filtering before grouping.
✅ Use HAVING for filtering after grouping.
✅ Works best with GROUP BY and aggregate functions.
✅ Supports multiple conditions using AND & OR.