The HAVING clause is used to filter grouped records after the GROUP BY clause has been applied.

Key difference:

  • WHERE → filters rows before grouping
  • HAVING → filters groups after aggregation

Basic syntax:

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;

HAVING is mainly used with aggregate functions like COUNT(), SUM(), AVG(), MIN(), MAX().

Step 1: Example table

Employees Table

EmployeeID Name Department Salary
1 John IT 5000
2 Jane HR 4500
3 Mike IT 6000
4 Sara HR 4700
5 Tom IT 5200

Step 2: HAVING with COUNT()

Find departments that have more than 2 employees:

SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 2;

Result:

Department TotalEmployees
IT 3

Step 3: HAVING with SUM()

Find departments where total salary is greater than 10,000:

SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 10000;

Result:

Department TotalSalary
IT 15200

Step 4: HAVING with AVG()

Find departments where average salary is above 5,000:

SELECT Department, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 5000;

Result:

Department AverageSalary
IT 5066.67

Step 5: WHERE vs HAVING (important difference)

❌ Wrong (aggregate in WHERE):

SELECT Department, COUNT(*)
FROM Employees
WHERE COUNT(*) > 2
GROUP BY Department;

❌ This will cause an error.

Correct (aggregate in HAVING):

SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 2;

Step 6: Using WHERE and HAVING together

Filter rows first, then filter groups:

SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
WHERE Salary > 4800
GROUP BY Department
HAVING COUNT(*) >= 2;

Explanation:

  • WHERE Salary > 4800 → filters employees first
  • GROUP BY Department → groups remaining rows
  • HAVING COUNT(*) >= 2 → filters grouped results

Step 7: HAVING with ORDER BY

SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 9000
ORDER BY TotalSalary DESC;

Categorized in:

SQL Server,