SQL HAVING

SQL GROUP BY HAVING Explained

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.

WHERE and HAVING can be used in the same query.

HAVINGs are used with aggregrates: COUNT, SUM, etc.

Example

#

Problem: List all countries with more than 2 suppliers.

copy

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

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

SQL Group By

SQL Alias

Syntax

#

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

#

SQL 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.

copy

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

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

Result: 3 records

Country	Count
France	11
Germany	11
USA	13

SQL HAVING, 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.

copy

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

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

Result: 3 records

Country	Count
France	11
Germany	11
Brazil	9

SQL HAVING with AVG BETWEEN

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

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

copy

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

SELECT FirstName, LastName, AVG(TotalAmount) AS Average 
  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

FirstName	LastName	Average
Miguel	Angel Paolino	1081.215000
Isabel	de Castro	1063.420000
Alexander	Feuer	1008.440000
Thomas	Hardy	1062.038461
Pirkko	Koskitalo	1107.806666
Janete	Limeira	1174.945454
Antonio	Moreno	1073.621428
Rita	Müller	1065.385000
José	Pedro Freyre	1183.010000
Carine	Schmitt	1057.386666