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.

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

Table: Department

Example of HAVING Clause

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

SELECT City,COUNT(ID) as 'No. of Employee by City'
FROM Employee
GROUP BY City HAVING COUNT(ID)>=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:

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;