Curriculum
Course: SQL
Login

Curriculum

SQL

SQL References

0/80

MySQL Functions

0/139

SQL Server Functions

0/84

SQL Quick Ref

0/1
Text lesson

SQL HAVING

The SQL HAVING Clause

The HAVING clause was introduced in SQL because aggregate functions cannot be utilized with the WHERE keyword.

HAVING Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Demo Database

Here is an excerpt from 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

SQL HAVING Examples

This SQL statement enumerates the number of customers in each country, including only those countries with more than 5 customers.

Example

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

This SQL statement displays the count of customers in each country, sorting them from highest to lowest, and including only countries with more than 5 customers.

Example

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

Demo Database

Here is an excerpt from 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

And here is a segment from 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 

More HAVING Examples

This SQL statement retrieves employees who have recorded more than 10 orders.

Example

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;

This SQL statement checks whether the employees “Davolio” or “Fuller” have logged more than 25 orders.

Example

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;