How to filter grouped entries with SQL HAVING

SQL HAVING is a condition that can be applied to already grouped entries. It works with aggregate functions to restrict result sets.

What is SQL HAVING?

In addition to the familiar WHERE clause, there is another condition in Structured Query Language that is frequently used: SQL HAVING. This condition filters data based on specific criteria. It is applied with the SQL command SELECT and the SQL GROUP BY statement. While GROUP BY groups results, SQL HAVING restricts the result set using different aggregate functions. The condition was introduced because WHERE cannot interact with aggregate functions such as SQL AVG(), SQL COUNT(), MAX(), MIN() and SUM(). SQL HAVING is used after the statements WHERE (if available) and GROUP BY, but before ORDER BY.

Syntax and function

To understand how SQL HAVING works and its benefits, it’s worth taking a look at the syntax of the clause:

SELECT name_of_column(n) 
FROM name_of_table 
WHERE condition 
GROUP BY name_of_column(n) 
HAVING condition 
ORDER BY name_of_column(n);

An aggregate function is often applied to one or more columns, followed by specifying the table name for localisation. The WHERE condition is optional. GROUP BY combines identical values into groups, which can be further filtered with HAVING and ordered with ORDER BY.

Example of the condition

The easiest way to illustrate SQL HAVING is with the help of a simple example. To do this, we create a small table called ‘Customer list’. This contains the columns ‘Customer number’, ‘Name’, ‘Location’ and ‘Item’:

Now we use SQL HAVING to find out how many customers from Barnsley have ordered ten or more items. To do this, we use the following code and specify the number using an SQL alias as ‘NumberOrders’:

SELECT Location, COUNT(Location) AS NumberOrders 
FROM CustomerList 
WHERE Location = 'Barnsley' 
GROUP BY location, article 
HAVING Article > 10;

The corresponding output is:

The condition combined with INNER JOIN

You can also combine SQL HAVING with the keyword INNER JOIN. To do this, we create a second table called ‘Article_01’, which stores how often and when a particular article was ordered. It contains an order number, a customer number, the quantity, and an order date and looks like this:

Now we can now instruct the system to show us all customers who have placed more than one order. The appropriate code is this one:

SELECT Customer list.name, COUNT(Article_01.order number) AS NumberOrders 
FROM (Article_01 
INNER JOIN Customer list ON Article_01.customer_number = Customer_01.customer_number) 
GROUP BY Name 
HAVING COUNT(Article_01.OrderNumber) > 1;

We get the following ouput:

Alternatives to SQL HAVING

An alternative to SQL HAVING is WHERE. However, the two options cannot be used in exactly the same way. WHERE is used for individual entries and can also be combined with DELETE or UPDATE in addition to SELECT. HAVING, on the other hand, is only intended for grouped entries and is only compatible with SELECT. WHERE is used before GROUP BY and HAVING after. In addition, only SQL HAVING can work with aggregate functions.