SQL Tutorial SQL Advanced SQL Database SQL References

SQL - HAVING Clause



The SQL HAVING clause is used to specify conditions with aggregate functions. Please note that SQL WHERE clause can not be used to specify conditions with aggregate functions.

Syntax

The syntax for using HAVING clause is given below:

SELECT column_name(s)
FROM table_name
WHERE condition(s)
GROUP BY column_name(s)
HAVING condition(s)
ORDER BY column_name(s);

Please note that the HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used.

Example:

Consider a database containing tables called Employee and Bonus_Paid with the following records:

Table 1: Employee table

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryLondon242750
3JoLondon272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangAmsterdam282800

Table 2: Bonus_Paid table

EmpIDBonus
1500
2400
3450
4550
5400
6600

  • To fetch a list containing number of employees (from Employee table) in each city and only including cities with more than one employees, the SQL code is given below:

    SELECT COUNT(EmpID) AS Number_of_Employee, City  
    FROM Employee
    GROUP BY City
    HAVING COUNT(EmpID) > 1;
    

    This will produce the result as shown below:

    Number_of_EmployeeCity
    2Amsterdam
    3London

    To get above table sorted in descending order based on number of employees, the SQL code will be:

    SELECT COUNT(EmpID), City  
    FROM Employee
    GROUP BY City
    HAVING COUNT(EmpID) > 1
    ORDER BY COUNT(EmpID) DESC;
    

    This result of the SQL code will be:

    Number_of_EmployeeCity
    3London
    2Amsterdam
  • Using HAVING clause with JOINs: To fetch a list containing bonus paid to employees in each city and only including cities with more than one employees, the SQL code is given below:

    SELECT A.City, SUM(B.Bonus) AS [Bonus Amount]
    FROM Employee AS A
    INNER JOIN Bonus_Paid AS B
    ON A.EmpID = B.EmpID
    GROUP BY City
    HAVING COUNT(A.EmpID) > 1;
    

    This result of the following code will be:

    CityBonus Amount
    Amsterdam1150
    London1350

5