Aggregation

HAVING

Filter groups after counting their members.

Lesson 28 of 84

Reading: about 2 min

Lesson + practice: about 12 min

Difficulty: Intermediate

On this page4 sections

This lesson uses the unmodified DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Filter groups after counting their members.

Run the example

sql

SELECT City, COUNT(*) AS CustomerCount
FROM dbo.Customers
GROUP BY City
HAVING COUNT(*) >= 3
ORDER BY City;

Read-only T-SQL against dataset v1.0.0

2 result rows on the unmodified v1.0.0 dataset.
City	CustomerCount
Birmingham	3
London	5

Cities with one or two customers are absent from this grouped result.

Find regions with at least five customers.

Check your answer and expected result

Reveal answer

sql

SELECT Region, COUNT(*) AS CustomerCount
FROM dbo.Customers
GROUP BY Region
HAVING COUNT(*) >= 5
ORDER BY Region;