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)