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.
HAVINGclause was added to SQL because the
WHEREkeyword could not be used with aggregate functions.
HAVINGclause specifies a filter condition for a group or an aggregate.
HAVINGclause is similar to
WHEREclause but the only difference is
WHEREclause is applied to the individual column whereas
HAVINGclause is applied to the groups-the row in the result set representing the groups.
HAVINGis usually used with a
GROUP BYclause, but even if you are not using
GROUP BYclause, you can use
HAVINGclause to function like a
SELECT column1,column2,....columnN FROM tableName WHERE CONDITION GROUP BY column(s) HAVING CONTITION;
Let us consider three tables "Employee" and "Department" for records
Table Name: Employee
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;
|City||No. of Employee by City|
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;
|DepartmentName||No. of Employee in Each Dept.|