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
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | London | 24 | 2750 |
3 | Jo | London | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Amsterdam | 28 | 2800 |
Table 2: Bonus_Paid table
EmpID | Bonus |
---|---|
1 | 500 |
2 | 400 |
3 | 450 |
4 | 550 |
5 | 400 |
6 | 600 |
-
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_Employee City 2 Amsterdam 3 London 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_Employee City 3 London 2 Amsterdam -
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:
City BonusAmount Amsterdam 1150 London 1350