SQLite COUNT Function

Patel prashant
Sep 4, 2018 · 4 min read

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: the COUNT function is applied to all values in the group. The SQLite COUNT function uses ALL by default, therefore, you can omit it.
  • DISTINCT: the COUNT function 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;

Try It

You can add the WHERE clause to find the number of tracks in album with id 10:

SELECT

count(*)

FROM

tracks

WHERE

albumid = 10;

Try It

To get album with the number of tracks for each album, you use GROUP BY clause:

SELECT

albumid,

count(*)

FROM

tracks

GROUP BY

albumid;

Try It

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

Try It

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;

Try It

SQLite COUNT(DISTINCT expression) examples

Let’s take a look at the employees table.

SELECT

employeeid,

lastname,

firstname,

title

FROM

employees;

Try It

To get the number of titles of employees, you pass the title column to the COUNT function as follows:

SELECT

COUNT(title)

FROM

employees;

Try It

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;

Try It

In this tutorial, we have shown you how to use SQLite COUNT function to count the number of items in a group.

GETTING STARTED






Patel prashant

Written by

Android Developer

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade