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;

Try It

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;

Try It

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;

Try It

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 the 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;

Try It

In this tutorial, you have learned how to use SQLite HAVING clause to specify the search condition for groups.