How SQLite Count() Works

The SQLite count() function can be used to return the number of rows in a result set.

It can also be used to return the number of times a given column is not NULL in the result set.

It can be used in two ways. If you pass in the asterisk (*) wildcard character, it will return the total number of rows in the group. If you provide the name of a column, it will return the number of times that column is not NULL.

Syntax

The syntax goes like this:

count(X)
count(*)

So in this case, X could be the name of a column, and the asterisk (*) wildcard is used to specify the total number of rows in the group.

Example: count(*)

Here’s a basic example to demonstrate the asterisk (*) syntax to return the number of rows in a table.

SELECT count(*) FROM Customer;

Result:

I ran this query on the Chinook sample database, and so it turns out that there are 59 rows in the Customer table.

Example: count(X)

In this example, I pass the name of a specific column of the table.

SELECT count(Fax) FROM Customer;

Result:

In this case, the Fax column has 12 non-NULL values.

In other words, 47 rows contain a NULL value in the Fax column.

Using a WHERE Clause

The result of count() is calculated after any WHERE clauses.

SELECT count(Fax) 
FROM Customer 
WHERE CustomerId <= 5;

Result:

In this case, there were only two non-NULL values in the Fax column within the result set.

Here’s what the results look like with columns returned (and without using the count() function).

SELECT CustomerId, Fax 
FROM Customer 
WHERE CustomerId <= 5;

Result:

CustomerId  Fax               
----------  ------------------
1           +55 (12) 3923-5566
2                             
3                             
4                             
5           +420 2 4172 5555

So we can see that only rows 1 and 5 have non-NULL values in the Fax column.

Using a LIMIT Clause

The result is not calculated after any LIMIT clause.

SELECT count(Fax) 
FROM Customer 
LIMIT 5;

Result:

But you can do something like this instead:

SELECT count(Fax) 
FROM (SELECT Fax FROM Customer 
LIMIT 5);

Result:

Grouped Results

One handy use case for count() is to use it in conjunction with the GROUP BY clause, so that multiple rows are returned – each one representing a group – with a count of the rows in that group.

Here’s an example.

SELECT 
  ar.Name, 
  count(al.Title)
FROM Album al
INNER JOIN Artist ar
ON ar.ArtistId = al.ArtistId
WHERE ar.Name LIKE 'D%'
GROUP BY ar.Name
LIMIT 10;

Result:

Name             count(al.Title)
---------------  ---------------
David Coverdale  1              
Deep Purple      11             
Def Leppard      1              
Dennis Chambers  1              
Djavan           2              
Dread Zeppelin   1

The DISTINCT Keyword

You can add the DISTINCT keyword to count only distinct values. In other words, you can remove duplicate values in the calculation.

For examples of this see How to Remove Duplicates from SQLite Count() Results.