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