SQL HAVING Clause
What does the HAVING clause do in a query?
The HAVING clause is like WHERE but operates on grouped records returned by a GROUP BY.
HAVING applies to summarized group records, whereas WHERE applies to individual records.
Only the groups that meet the HAVING criteria will be returned.
HAVING requires that a GROUP BY clause is present.
Both WHERE and HAVING can be used in the same query at the same time.
The SQL HAVING syntax
The general syntax is
SELECT column-names FROM table-name WHERE condition GROUP BY column-names HAVING condition
The general syntax with ORDER BY is:
SELECT column-names FROM table-name WHERE condition GROUP BY column-names HAVING condition ORDER BY column-names
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
SQL GROUP BY Examples
Problem: List the number of customers in each country.
Only include countries with more than 10 customers.
Only include countries with more than 10 customers.
SELECT COUNT(Id), Country FROM Customer GROUP BY Country HAVING COUNT(Id) > 10
Result: 3 records
Count | Country |
---|---|
11 | France |
11 | Germany |
13 | USA |
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
Problem: List the number of customers in each country,
except the USA, sorted high to low.
Only include countries with 9 or more customers.
except the USA, sorted high to low.
Only include countries with 9 or more customers.
SELECT COUNT(Id), Country FROM Customer WHERE Country <> 'USA' GROUP BY Country HAVING COUNT(Id) >= 9 ORDER BY COUNT(Id) DESC
Result: 3 records
Count | Country |
---|---|
11 | France |
11 | Germany |
9 | Brazil |
ORDER |
---|
Id |
OrderDate |
OrderNumber |
CustomerId |
TotalAmount |
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
Problem: List all customer with average orders
between $1000 and $1200.
between $1000 and $1200.
SELECT AVG(TotalAmount), FirstName, LastName FROM [Order] O JOIN Customer C ON O.CustomerId = C.Id GROUP BY FirstName, LastName HAVING AVG(TotalAmount) BETWEEN 1000 AND 1200
Result: 10 records
Average | FirstName | LastName |
---|---|---|
1081.215000 | Miguel | Angel Paolino |
1063.420000 | Isabel | de Castro |
1008.440000 | Alexander | Feuer |
1062.038461 | Thomas | Hardy |
1107.806666 | Pirkko | Koskitalo |
1174.945454 | Janete | Limeira |
1073.621428 | Antonio | Moreno |
1065.385000 | Rita | Müller |
1183.010000 | José | Pedro Freyre |
1057.386666 | Carine | Schmitt |