How To Use HAVING Clause in SQL Server
HAVING clause is used to filter the groups in a select statement. It is used after the aggregate functions like MIN, MAX, AVG etc. are performed. First, we retrieve data using GROUP BY clause, then we can filter those groups using HAVING clause.
WHERE clause is also used to filter the data, but it is not used with aggregate functions like, MIN, MAX, AVG etc. WHERE clause is used to filter the rows, whereas HAVING clause is used to filter the groups.
HAVING and WHERE can be used together in a SELECT query, In this case WHERE clause is used first to filter the rows, and then the filtered rows are grouped, and aggregate calculations are performed. Then HAVING clause filter the grouped data.
From a performance point of view HAVING is slower than WHERE and should be avoided when possible. Because HAVING clause first perform the aggregate calculations and group the data and then filter those groups.
In this demo, we will create a table tblCustomers and insert records from NORTHWIND database Customers table.
Let’s insert records in tblCustomers table from NORTHWIND database Customers table. CustomerID is an identity column, therefore we did not mention in INSERT INTO statement.
CREATE TABLE tblCustomers ( CustomerID INT PRIMARY KEY IDENTITY(1,1), CustomerName NVARCHAR(50), ContactTitle NVARCHAR(50), City NVARCHAR(50), Region NVARCHAR(15), Country NVARCHAR(50), Phone NVARCHAR(20) )
INSERT INTO tblcustomers (CustomerName,ContactTitle,City,Region,Country,Phone) SELECT CustomerName,ContactTitle,City,Region,Country,Phone FROM [NORTHWIND].[dbo].[Customers]
We can see 10 records as a sample in the following image.

In the below example, we will retrieve records from tblCustomers table using HAVING clause where there are more than 10 customers in each country.
SELECT Country, COUNT(1) AS TotalCustomers FROM tblCustomers GROUP BY Country HAVING COUNT(1)>10
The output of the above query with HAVING clause is shown below.

WHERE and HAVING clause can be used altogether in a SELECT statement. WHERE clause will be used before the GROUP BY clause and HAVING clause will be used after the GROUP BY clause.
In the following example, First we will filter the customers who belong to the USA, FRANCE and UK, group them as per countries and then filter those groups to find countries having more than 10 customers.
SELECT Country, COUNT(1) AS TotalCustomers FROM tblCustomers WHERE Country IN ('USA','FRANCE','UK') GROUP BY Country HAVING COUNT(1)>10
Recommended Readings
- Advanced SQL Queries For Practice With Solution
- SQL Queries For Practice With Solution
- SQL Interview Questions and Answers
- STORED PROCEDURE in SQL Server
- How To Join Tables Data in SQL Server
- How to use Transaction in SQL Stored Procedure
- Difference Between IN and NOT IN Operators in SQL
- How To Modify Date in SQL Using DATEADD
- How To Get Year From Date in SQL Server
- How To Get Month From Date in SQL Server
- How To Get Day From Date in SQL Server
- How To Use ROW_NUMBER Function in SQL
- Date and Time Functions in SQL Server
- How To Find Nth Highest Salary in SQL Server
- How to Backup Table Using SELECT INTO Statement
- How To Use HAVING Clause in SQL Server
- Aggregate Functions in SQL Server
- How To Group Data Using Group By in SQL Server
- How To Truncate Table in SQL Server
- How To Delete Data From Table in SQL Server
- How To Update Table Data in SQL Server
- How To Sort Data Using Order By Clause in SQL
- How To Select Distinct Records in SQL Server
- How to Filter Data From Table in SQL Server
- Round Off Values Using Ceiling and Floor in SQL
- How To Find Square Root Of A Number in SQL Server
- How To Select Data From Table in SQL Server
- How To Insert Data in SQL Server Table
- How To Add NOT NULL Constraint in SQL Server
- How To Add Check Constraint on SQL Server Table
- How To Add Default Constraint on SQL Server Table
- Unique Key Constraint in SQL Server
- How to add Foreign Key Constraint in SQL Server
- How To Add Identity To SQL Server Table Column
- How to add Primary Key Constraint in SQL Server
- How To Create Alter and Drop Table in SQL Server
- How To Create Alter and Drop Database in SQL