HAVING Clause

A HAVING clause in SQL is an optional clause of the SELECT statement, it only returns row where aggregate values meet the specified conditions. The HAVING clause is used to filter the data on the group row but not on the individual row. 

Note : HAVING clause filter group of rows while WHERE clause filters individual rows.

Syntax:

SELECT Column1, Column2,
Aggregate_function(expression1)
  FROM Table_Name
[WHERE Clause]
GROUP BY Column1, Column2
HAVING Having_Condition;

Example:
Table Name “EMPLOYEE”
ID
NAME
ADDRESS
DEPARTMENT
SALARY
101
Rajesh
Ghaziabad
IT
10000
102
Swati
Delhi
Non IT
8000
103
Manoj
Noida
IT
15000
104
Sachin
Noida
IT
12000
105
Swati
Delhi
Non IT
8000
·         Write a query to find Department where total salary is greater than 20000.
SELECT DEPARTMENT,
           SUM(SALARY) as TOTAL_SALARY,
           COUNT(ID) as NO_OF_EMP
  FROM EMPLOYEE
GROUP BY DEPARTMENT
HAVING SUM(SALARY) > 20000;
Output of the above query will be:

DEPARTMENT
TOTAL_SALARY
NO_OF_EMP
IT
37000
3
·         Write a query to find the address where more than one employee.
SELECT ADDRESS, COUNT(ID) as NO_OF_EMP
  FROM EMPLOYEE
GROUP BY ADDRESS
HAVING COUNT(ID) > 1;
Output of the above query will be:

ADDRESS
NO_OF_EMP
Noida
1
Delhi
2
·         Write a query to find out duplicate name Employee.
SELECT NAME, COUNT(ID)
  FROM EMPLOYEE
GROUP BY NAME
HAVING COUNT(ID) > 1;
Output of the above query will be:

Name
NO_OF_EMP
Swati
2