SQL - HAVING Clause

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 table in all examples here.

Employee Table

EmpId	FirstName	LastName	Email	PhoneNo	Salary	DeptId
1	'John'	'King'	'[email protected]'	'650.127.1834'	33000	1
2	'James'	'Bond'				1
3	'Neena'	'Kochhar'	'[email protected]'	'123.456.4568'	17000	2
4	'Lex'	'De Haan'	'[email protected]'	'123.000.4569'	15000	1
5	'Amit'	'Patel'			18000	1
6	'Abdul'	'Kalam'	'[email protected]'	'123.123.0000'	25000	2

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

SQL Script: GROUP BY

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

The above query will display the following result.

DeptId	No of Employees
1	4
2	2

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

SQL Script: GROUP BY

SELECT DeptId, COUNT(EmpId) as 'Number of Employees' 
FROM Employee
GROUP BY DeptId;
HAVING COUNT(EmpId) &gt; 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.

DeptId	No of Employees
1	4

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 Script: GROUP BY

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