SQL HAVING

HAVING is like WHERE but operates on grouped records.

HAVING requires that a GROUP BY clause is present.

Groups that meet the HAVING criteria will be returned.

HAVING is used with aggregrates: COUNT,
MAX,
SUM, etc.

List all countries with more than 2 suppliers.

SELECT Country, COUNT(Id) AS Suppliers
  FROM Supplier
 GROUP BY Country
HAVING COUNT(Id) > 2

Result:  3 records

Country
Suppliers

France
3

Germany
3

USA
4

HAVING syntax.

SELECT column-names
  FROM table-name
 WHERE condition
 GROUP BY column-names
HAVING condition

HAVING syntax with ORDER BY.

SELECT column-names
  FROM table-name
 WHERE condition
 GROUP BY column-names
HAVING condition
 ORDER BY column-names

More Examples

#

HAVING with COUNT

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

Problem: List the number of customers in each country.
Only include countries with more than 10 customers.

SELECT Country, COUNT(Id) AS Customers
  FROM Customer
 GROUP BY Country
HAVING COUNT(Id) > 10

Result:  3 records

Country
Customers

France
11

Germany
11

USA
13

HAVING and ORDER BY

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.

SELECT Country, COUNT(Id) AS Customers
  FROM Customer
 WHERE Country <> 'USA'
 GROUP BY Country
HAVING COUNT(Id) >= 9
 ORDER BY COUNT(Id) DESC

Result:  3 records

Country
Customers

France
11

Germany
11

Brazil
9

HAVING with AVG BETWEEN

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

Problem: List customer with average orders between $1000 and $1200.

SELECT FirstName, LastName, 
       CONVERT(DECIMAL(10,2), AVG(TotalAmount)) AS 'Average  Order'
  FROM [Order] O 
  JOIN Customer C ON O.CustomerId = C.Id
 GROUP BY FirstName, LastName
HAVING AVG(TotalAmount) BETWEEN 1000 AND 1200

Note: CONVERT formats the average value to 2 decimals.

Result:  10 records

FirstName
LastName
Average Order

Miguel
Angel Paolino
1081.22

Isabel
de Castro
1063.42

Alexander
Feuer
1008.44

Thomas
Hardy
1062.04

Pirkko
Koskitalo
1107.81

Janete
Limeira
1174.95

Antonio
Moreno
1073.62

Rita
Müller
1065.39

José
Pedro Freyre
1183.01

Carine
Schmitt
1057.39

You may also like

#