SQL HAVING Clause

  • HAVING filters records that work on summarized GROUP BY results.
  • 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.
  • WHERE and HAVING can be in the same query.
The definitive guide
for data professionals


Order today!
 See 2 min video



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




SQL GROUP BY Examples



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

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


Results: 3 records

Count Country
11 France
11 Germany
13 USA



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 COUNT(Id), Country 
  FROM Customer
 WHERE Country <> 'USA'
 GROUP BY Country
HAVING COUNT(Id) >= 9
 ORDER BY COUNT(Id) DESC


Results: 3 records

Count Country
11 France
11 Germany
9 Brazil



Problem: List all customer with average orders 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


Results: 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