SQL Server - HAVING Clause

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

Syntax

The syntax for using HAVING clause in SQL Server (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

Table 2: Bonus_Paid table

• 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