The SQL HAVING Clause: Usage and Examples

In SQL Server, the HAVING clause is used to filter records after the GROUP BY clause has been applied. Unlike the WHERE clause, which is used to filter rows before aggregation, the HAVING clause is used to filter groups based on aggregate functions like SUM, COUNT, AVG, etc.

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Syntax:

SELECT column1, aggregate_function(column2)
FROM table
GROUP BY column1
HAVING condition;

Prerequisite Codes

If you don't have table please create below table and practice it.

My database name is SQLExamples

USE SQLExamples

Create this below Employee table

DROP TABLE Employee
CREATE TABLE Employee(
EmpId INT,
EmpName VARCHAR(25),
EmpAddress VARCHAR(100),
EmpDept VARCHAR(25)
)

Insert data inside table

INSERT INTO Employee VALUES
(1, 'Rambo', 'Kolkata', 'IT'),
(2, 'Rohit', 'Kolkata', 'IT'),
(3, 'Rohon', 'Kolkata', 'ITIS'),
(4, 'Ronok', 'Kolkata', 'ITIS'),
(5, 'Rubin', 'Kolkata', 'ITIS'),
(6, 'Sorif', 'Kolkata', 'ADMIN'),
(7, 'Soriful', 'Kolkata', 'ADMIN'),
(8, 'Sofik', 'Kolkata', 'ADMIN')

SELECT * FROM Employee

Output:

The above code will produce the following result-

Example 1:

Code:

SELECT EmpDept, COUNT(EmpDept) AS DeptCount 
FROM Employee
GROUP BY EmpDept 
HAVING COUNT(EmpId) > 2

Output:

The above code will produce the following result-

Difference between WHERE and HAVING clause:

• WHERE clause can be used with - Select, Insert, and Update statements, where as HAVING clause can only be used with the Select statement.
• WHERE filters rows before aggregation (GROUPING), where as, HAVING filters groups, after the aggregations are performed.
• Aggregate functions cannot be used in the WHERE clause, unless it is in a sub query contained in a HAVING clause, whereas, aggregate functions can be used in Having clause.