Summary: in this tutorial, you will learn how to use SQLite
HAVING clause to specify a filter condition for a group or an aggregate.
Introduction to SQLite
HAVING clause is an optional clause of the
SELECT statement. The
HAVING clause specifies a search condition for a group.
You often use the
HAVING clause with the
GROUP BY clause. The
GROUP BY clause groups a set of rows into a set of summary rows or groups. Then the
HAVING clause filters groups based on a specified condition.
If you use the
HAVING clause, you must include the
GROUP BY clause; otherwise, you will get the following error:
Error: a GROUP BY clause is required before HAVING
Note that the
HAVING clause is applied after
GROUP BY clause, whereas the
WHERE clause is applied before the
GROUP BY clause.
The following illustrates the syntax of the
In this syntax, the
HAVING clause evaluates the
search_condition for each group as a Boolean expression. It only includes a group in the final result set if the evaluation is true.
HAVING clause examples
We will use the
tracks table in the sample database for demonstration.
To find the number of tracks for each album, you use
GROUP BY clause as follows:
To find the numbers of tracks for the album with id 1, we add a
HAVING clause to the following statement:
HAVING albumid = 1;
We have referred to the
AlbumId column in the
To find albums that have the number of tracks between 18 and 20, you use the aggregate function in the
HAVING clause as shown in the following statement:
COUNT(albumid) BETWEEN 18 AND 20
ORDER BY albumid;
HAVING clause with
INNER JOIN example
The following statement queries data from
albums tables using inner join to find albums that have a total length greater than 60,000,000 milliseconds.
SUM(Milliseconds) AS length
INNER JOIN albums ON albums.AlbumId = tracks.AlbumId
length > 60000000;
In this tutorial, you have learned how to use SQLite
HAVING clause to specify the search condition for groups.