Summary: in this tutorial, you will learn how to use SQLite COUNT function to get the number of items in a group.
Introduction to SQLite COUNT function
SQLite COUNT is an aggregate function that returns the number of items in a group. For example, you can use the COUNT function to get the number of tracks, the number of artists, playlists and the number of tracks in each playlist, and so on.
The following illustrates the syntax of the COUNT function:
1 | COUNT([ALL | DISTINCT] expression); |
Arguments
The COUNT function behaves based on the arguments that you pass in. The following are arguments of the COUNT function:
ALL: theCOUNTfunction is applied to all values in the group. TheCOUNTfunction usesALLby default, therefore, you can omit it.DISTINCT: theCOUNTfunction only considers unique non-null values.- expression: is any expression that can be a column of a table.
SQLite COUNT(*) function
Another form of the COUNT function is as follows:
1 | COUNT(*) |
The COUNT(*) function returns the number of rows in a table, including the rows that contain NULL values. The COUNT(*) function counts each row individually. It takes no parameters other than asterisk symbol (*).
SQLite COUNT function examples
Let’s take few examples to see the COUNT function works.
SQLite COUNT(*) example
We will take the tracks table in the sample database to demonstrate the functionality of the COUNT function.
To get the number of rows in the tracks table, you use the COUNT(*) function as follows:
1 2 3 4 | SELECT count(*) FROM tracks; |
You can add the WHERE clause to find the number of tracks in the album with id 10:
1 2 3 4 5 6 | SELECT count(*) FROM tracks WHERE albumid = 10; |
To get albums and the number of tracks for each, you use the COUNT function with the GROUP BY clause as shown in the following query:
1 2 3 4 5 6 7 | SELECT albumid, count(*) FROM tracks GROUP BY albumid; |
If you want to find the albums that have more than 25 tracks, you use the HAVING clause:
1 2 3 4 5 6 7 8 | SELECT albumid, count(*) FROM tracks GROUP BY albumid HAVING count(*) > 25 |
The result set is not so informative. You need to add the album’s name and sort the album by the number of tracks.
To do this, you add the INNER JOIN and ORDER BY clauses to the query like the following query:
1 2 3 4 5 6 7 8 9 10 11 | SELECT tracks.albumid, name, count(*) FROM tracks INNER JOIN albums on albums.albumid = tracks.albumid GROUP BY tracks.albumid HAVING count(*) > 25 ORDER BY COUNT(*) desc; |
SQLite COUNT(DISTINCT expression) examples
Let’s take a look at the employees table.
1 2 3 4 5 6 7 | SELECT employeeid, lastname, firstname, title FROM employees; |
To get the number of titles of employees, you pass the title column to the COUNT function as follows:
1 2 3 4 | SELECT COUNT(title) FROM employees; |
However, to get the number of unique titles, you need to pass the DISTINCT clause to the COUNT function as the following statement:
1 2 3 4 | SELECT COUNT(DISTINCT title) FROM employees; |
In this tutorial, we have shown you how to use SQLite COUNT function to count the number of items in a group.