T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL - HAVING Clause



The T-SQL (Transact-SQL) HAVING clause is used to specify conditions with aggregate functions. Note that the T-SQL (Transact-SQL) WHERE clause can not be used to specify conditions with aggregate functions.

Syntax

The syntax for using HAVING clause in T-SQL (Transact-SQL) 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 query 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 query will be:

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

    This result of the query 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 query is given below:

    SELECT A.City, SUM(B.Bonus) AS BonusAmount
    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:

    CityBonusAmount
    Amsterdam1150
    London1350