SQLite COUNT Function
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 SQLite COUNT function:
COUNT([ALL | DISTINCT] expression);
Arguments
The SQLite 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. The SQLite COUNT function 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:
COUNT(*)
The COUNT(*) function returns the number of rows in a table, including the rows that contain NULLvalues. The COUNT(*) function counts each row individually. It takes no parameters other than asterisk symbol (*).
SQLite COUNT 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 COUNTfunction.

To get the number of rows in the tracks table, you use the COUNT(*) function as follows:
SELECT
count(*)
FROM
tracks;

You can add the WHERE clause to find the number of tracks in album with id 10:
SELECT
count(*)
FROM
tracks
WHERE
albumid = 10;

To get album with the number of tracks for each album, you use GROUP BY clause:
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:
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 as the following query:
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.

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:
SELECT
COUNT(title)
FROM
employees;

However, to get the number of unique titles, you need to pass the DISTINCT clause to the COUNTfunction as the following statement:
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.
