The HAVING clause in SQL was introduced because the WHERE keyword cannot filter results based on aggregate functions directly.
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s); |
Here is a portion of the “Customers” table in the Northwind sample database:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
1 |
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
4 |
Around the Horn |
Thomas Hardy |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
Berglunds snabbköp |
Christina Berglund |
Berguvsvägen 8 |
Luleå |
S-958 22 |
Sweden |
The following SQL statement lists the number of customers in each country, filtering to include only countries with more than 5 customers:
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5; |
The following SQL statement lists the number of customers in each country, sorting from highest to lowest, and includes only countries with more than 5 customers:
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC; |
Here is a portion of the “Orders” table in the Northwind sample database:
OrderID |
CustomerID |
EmployeeID |
OrderDate |
ShipperID |
10248 |
90 |
5 |
1996-07-04 |
3 |
10249 |
81 |
6 |
1996-07-05 |
1 |
10250 |
34 |
4 |
1996-07-08 |
2 |
Here is a portion of the “Employees” table:
EmployeeID |
LastName |
FirstName |
BirthDate |
Photo |
Notes |
1 |
Davolio |
Nancy |
1968-12-08 |
EmpID1.pic |
Education includes a BA…. |
2 |
Fuller |
Andrew |
1952-02-19 |
EmpID2.pic |
Andrew received his BTS…. |
3 |
Leverling |
Janet |
1963-08-30 |
EmpID3.pic |
Janet has a BS degree…. |
The following SQL statement lists employees who have registered more than 10 orders:
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID) GROUP BY LastName HAVING COUNT(Orders.OrderID) > 10; |
The following SQL statement checks whether the employees “Davolio” or “Fuller” have registered more than 25 orders:
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID WHERE LastName = ‘Davolio’ OR LastName = ‘Fuller’ GROUP BY LastName HAVING COUNT(Orders.OrderID) > 25; |