SQL HAVING Clause

What does the HAVING clause do in a query?

  • HAVING 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.
  • WHERE and HAVING can be used in the same query.

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


Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas