SQLite Having
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
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 HAVING
clause:
SELECT
column_1,
column_2,
aggregate_function (column_3)
FROM
table
GROUP BY
column_1,
column_2
HAVING
search_condition;
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.
SQLite 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:
SELECT
albumid,
COUNT(trackid)
FROM
tracks
GROUP BY
albumid;
To find the numbers of tracks for the album with id 1, we add a HAVING
clause to the following statement:
SELECT
albumid,
COUNT(trackid)
FROM
tracks
GROUP BY
albumid
HAVING albumid = 1;
We have referred to the AlbumId
column in the HAVING
clause.
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:
SELECT
albumid,
COUNT(trackid)
FROM
tracks
GROUP BY
albumid
HAVING
COUNT(albumid) BETWEEN 18 AND 20
ORDER BY albumid;
SQLite HAVING
clause with INNER JOIN
example
The following statement queries data from tracks
and albums
tables using inner join to find albums that have a total length greater than 60,000,000 milliseconds.
SELECT
tracks.AlbumId,
title,
SUM(Milliseconds) AS length
FROM
tracks
INNER JOIN albums ON albums.AlbumId = tracks.AlbumId
GROUP BY
tracks.AlbumId
HAVING
length > 60000000;
In this tutorial, you have learned how to use SQLite HAVING
clause to specify the search condition for groups.