In SQL, the HAVING clause can be used to specify a search condition for a group or an aggregate.

The HAVING clause is typically used with the GROUP BY clause. In cases where it’s not, there is an implicit single, aggregated group.

The HAVING clause is similar to the WHERE clause, except that WHERE filters individual rows, whereas HAVING filters groups. The WHERE clause filters data before it is grouped, whereas HAVING filters data after it is grouped.

Example 1 – HAVING with COUNT()

Suppose we have the following table:

SELECT * FROM Pets;

Result:

+---------+-------------+-----------+-----------+------------+
| PetId   | PetTypeId   | OwnerId   | PetName   | DOB        |
|---------+-------------+-----------+-----------+------------|
| 1       | 2           | 3         | Fluffy    | 2020-11-20 |
| 2       | 3           | 3         | Fetch     | 2019-08-16 |
| 3       | 2           | 2         | Scratch   | 2018-10-01 |
| 4       | 3           | 3         | Wag       | 2020-03-15 |
| 5       | 1           | 1         | Tweet     | 2020-11-28 |
| 6       | 3           | 4         | Fluffy    | 2020-09-17 |
| 7       | 3           | 2         | Bark      | NULL       |
| 8       | 2           | 4         | Meow      | NULL       |
+---------+-------------+-----------+-----------+------------+

We could run the following query against this table:

SELECT 
    PetTypeId, 
    COUNT(PetTypeId) AS Count
FROM Pets
GROUP BY PetTypeId
HAVING COUNT(PetTypeId) > 2
ORDER BY Count DESC;

Result:

+-------------+---------+
| PetTypeId   | Count   |
|-------------+---------|
| 3           | 4       |
| 2           | 3       |
+-------------+---------+

In this case, we used the HAVING clause in conjunction with the GROUP BY clause to return just those rows that have a COUNT(PetTypeId) of greater than 2. This uses the COUNT() function, which is a SQL standard aggregate function available in most major RDBMSs.

If we omit the HAVING clause, we get one more result:

SELECT 
    PetTypeId, 
    COUNT(PetTypeId) AS Count
FROM Pets
GROUP BY PetTypeId
ORDER BY Count DESC;

Result:

+-------------+---------+
| PetTypeId   | Count   |
|-------------+---------|
| 3           | 4       |
| 2           | 3       |
| 1           | 1       |
+-------------+---------+

Example 2 – HAVING with SUM()

Here’s another example. This time we use the HAVING clause with the SUM() function, which is another aggregate function available in most major RDBMSs (see SQLite SUM() for another example).

SELECT
    CountryCode,
    District,
    SUM(Population) AS Population
FROM City
WHERE CountryCode IN ('AGO', 'ARE', 'AUS')
GROUP BY CountryCode, District
HAVING SUM(Population) > 1000000
ORDER BY CountryCode;

Result:

+---------------+-----------------+--------------+
| CountryCode   | District        | Population   |
|---------------+-----------------+--------------|
| AGO           | Luanda          | 2022000      |
| AUS           | New South Wales | 3993949      |
| AUS           | Queensland      | 1805236      |
| AUS           | Victoria        | 2990711      |
| AUS           | West Australia  | 1096829      |
+---------------+-----------------+--------------+

Example 3 – HAVING with the IN Operator

You’re not limited to just the =, <, >=, IN, LIKE, etc).

Here’s an example that uses the IN operator to specify a range of aggregate values to return.

SELECT
    CountryCode,
    District,
    SUM(Population) AS Population
FROM City
WHERE CountryCode IN ('AGO', 'ARE', 'AUS')
GROUP BY CountryCode, District
HAVING SUM(Population) IN (2022000, 3993949, 2990711)
ORDER BY CountryCode;

Result:

+---------------+-----------------+--------------+
| CountryCode   | District        | Population   |
|---------------+-----------------+--------------|
| AGO           | Luanda          | 2022000      |
| AUS           | New South Wales | 3993949      |
| AUS           | Victoria        | 2990711      |
+---------------+-----------------+--------------+

Example 4 – HAVING without a GROUP BY Clause

Although HAVING is usually used with the GROUP BY clause, it can also be used without it. When used without it, there is an implicit single, aggregated group.

The results you get may depend on your DBMS, but here’s an example that was done in SQL Server.

SELECT
    SUM(Population) AS Population
FROM City
HAVING SUM(Population) > 2000000;

Result:

+--------------+
| Population   |
|--------------|
| 1429559884   |
+--------------+

This example simply returns the aggregate population of all cities in the table.

Here’s what happens if we swap the greater than operator (>) with a less than operator (<).

SELECT
    SUM(Population) AS Population
FROM City
HAVING SUM(Population) < 2000000;

Result:

(0 rows affected)