SQL Server - HAVING Clause

In SQL Server, the HAVING clause includes one or more conditions that should be TRUE for groups of records. It is like the WHERE clause of the GROUP BY clause. The only difference is that the WHERE clause cannot be used with aggregate functions, whereas the HAVING clause can use aggregate functions.

The HAVING clause always comes after the GROUP BY clause and before the ORDER BY clause.

Syntax:

SELECT column1, column2,...columnN 
FROM table_name
[WHERE]
[GROUP BY column1, column2...columnN]
[HAVING conditions]
[ORDER BY]

HAVING Characteristics:

The HAVING clause is used to filter out grouping records.
The HAVING clause must come after the GROUP BY clause and before the ORDER BY clause.
The HAVING clause can include one or more conditions.
The HAVING condition can only include columns that are used with the GROUP BY clause. To use other columns in the HAVING condition, use the aggregate functions with them.

For the demo purpose, we will use the following Employee and Department tables in all examples here.

In the GROUP BY section, we used the following query to retrieve the no of employees in each department, as shown below.

SQL Server: GROUP BY

SELECT DeptId, COUNT(EmpId) as 'Number of Employees' 
FROM Employee
GROUP BY DeptId;

Now, to filter the result of the above GROUP BY query, use the HAVING clause with the aggregate function, as shown below.

SQL Server: GROUP BY

SELECT DeptId, COUNT(EmpId) as 'Number of Employees' 
FROM Employee
GROUP BY DeptId
HAVING COUNT(EmpId) > 2

Notice that we used an aggregate function COUNT() in the HAVING clause because EmpId is not included in the GROUP BY clause. The above query will display the following result.

The following query will throw an error because the Salary column is not included in the GROUP BY clause and does not use an aggregate function.

SQL Server: GROUP BY

SELECT DeptId, COUNT(EmpId) as 'Number of Employees' 
FROM Employee
GROUP BY DeptId;
HAVING Salary > 15000