SQL HAVING Clause

What is having Clause?

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

A HAVING clause in SQL specifies that an SQL SELECT statement must only return rows where aggregate values meet the specified conditions. After the aggregating operation, HAVING is applied, filtering out the rows that don't match the specified conditions.

Why we cannot use where with aggregate function?

We cannot use the WHERE clause with aggregate functions because it works for filtering individual rows. In contrast, HAVING can works with aggregate functions because it is used to filter groups.

Basic Syntax:

SELECT column_names

FROM tableName

WHERE condition

GROUP BY column_names

HAVING condition

ORDER BY column_names

EmployeeDetail Table:

ProjectDetail Table:

Below Sample Queries using Having Clause:

/*Write Down the query to fetch the number of employees in each department.
Only include department with more than 2 employees */

SELECT Department, count(EmpID) As NoOfEmploees 
FROM EmployeeDetail
GROUP BY Department
HAVING count(EmpID)>2
ORDER BY count(EmpID) DESC

--Write Down the query to fetch Project name assign more than one employee

SELECT ProjectName, count(*) As NoOfEmployees 
FROM ProjectDetail
GROUP BY ProjectName
HAVING count(*)>1

--Write Down the query to fetch Employee name(FirstName) assign more than one Project.

SELECT ed.FirstName,Count(ProjectName) NoOfProjects FROM EmployeeDetail ed
INNER JOIN ProjectDetail pd
ON ed.EmpId=pd.EmpId
GROUP BY FirstName
HAVING count(*)>1

/*Write Down the query to fetch if the Employees 'Frank' or 'Tom' have assigned with 
more than 2 Projects*/

SELECT ed.FirstName,Count(ProjectName) NoOfProjects FROM EmployeeDetail ed
INNER JOIN ProjectDetail pd
ON ed.EmpId=pd.EmpId
WHERE ed.FirstName='Frank' OR ed.FirstName='Tom'
GROUP BY FirstName
HAVING count(*)>1

Please refer below YouTube video on HAVING Clause: