;

SQL HAVING


In this tutorial, we will learn how to filter groups returned record by a GROUP BY clause. HAVING clause is used to filter groups returned record.

SQL HAVING Clause

  • SQL HAVING  clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
  • SQL HAVING clause specifies a filter condition for a group or an aggregate.
  • SQL HAVING clause is similar to WHERE clause but the only difference is WHERE clause is applied to the individual column whereas HAVING clause is applied to the groups-the row in the result set representing the groups.
  • SQL HAVING is usually used with a GROUP BY clause, but even if you are not using GROUP BY clause, you can use HAVING clause to function like a WHERE clause.
Syntax - HAVING Clause
SELECT column1,column2,....columnN
FROM tableName
WHERE CONDITION
GROUP BY column(s)
HAVING CONTITION;

Example:

Let us consider three tables "Employee" and "Department"  for records 

Table Name: Employee

ID EmpName City Country Gender Salary DeptID
1 Shankar Delhi India male 25000 1
2 Sourabh Delhi India male 30000 1
3 Ranvijay Mumbai India male 15000 2
4 Kapil Noida India male 25000 3
5 Shalini Jaipur India female 18000 2
6 Rakesh Faridabad India male 23000 1
7 Akshay Mumbai India male 21000 2
8 Sarah New York US female 76000 3
9 Rocky Noida India male 28000 3

Table: Department

DeptID DepartmentName
1 IT
2 Finance
3 HR

Example of HAVING Clause

For selecting a city name where the number of employees is greater than equals to 2,  a query will be:

Example - HAVING Clause
SELECT City,COUNT(ID) as 'No. of Employee by City'
FROM Employee
GROUP BY City HAVING COUNT(ID)>=2;
Output
City No. of Employee by City
Delhi 2
Mumbai 2
Noida 2

Example of HAVING Clause Using JOIN

For finding the name of the department having Employee HAVING number of employees more than 2, a query will be:

Example - HAVING Clause using JOIN
SELECT Dept.DepartmentName,COUNT(Emp.DeptId) AS 'No. of Employee in Each Dept.'
FROM Employee AS Emp
JOIN Department AS Dept ON Emp.DeptId=Dept.DeptID
GROUP BY Dept.DepartmentName,Emp.DeptID  HAVING COUNT(Emp.DeptId)>2;
Output
DepartmentName No. of Employee in Each Dept.
IT 3