SQL HAVING Clause

The HAVING clause in SQL is used to filter the results of a GROUP BY query. It allows you to apply conditions based on aggregate functions (like COUNT(), SUM(), AVG(), etc.) to the grouped data.

HAVING: Definition and Usage

The HAVING clause is essential for filtering grouped data. It comes after the GROUP BY clause and lets you specify conditions on the aggregated values. You can't use aggregate functions directly in a WHERE clause because the WHERE clause filters individual rows *before* grouping occurs. HAVING, on the other hand, filters the grouped results *after* the grouping has been performed.

Syntax


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

Example Databases

The examples below use data from the 'Customers', 'Orders', and 'Employees' tables.

Customers Table (Sample Data)

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

Orders Table (Sample Data)

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

Employees Table (Sample Data)

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

Examples

Filtering Groups (Countries with More Than 5 Customers)

This query counts customers per country and shows only countries with more than 5 customers.

Syntax


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

Output


(Empty set - The sample data doesn't contain any country with more than 5 customers)

Filtering and Sorting Groups

This query does the same grouping as above but also sorts the results, showing the countries with the most customers first.

Syntax


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

Output


(Empty set - The sample data doesn't contain any country with more than 5 customers)

More Complex HAVING Example

This query shows employees who have registered more than 10 orders.

Syntax


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;

Output


LastName | NumberOfOrders
-----------------------
(Employees with more than 10 orders would be listed here)

HAVING with Multiple Conditions

This query checks if employees 'Davolio' or 'Fuller' have registered more than 25 orders.

Syntax


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;

Output


LastName | NumberOfOrders
-----------------------
(Davolio or Fuller, if they have more than 25 orders, would be listed here)