SQLite count() Function

The SQLite count() function counts all the specified values ​​in a group and returns it.

Syntax

Here is the syntax of the SQLite count() function:

count(expr)

Parameters

expr

Required. A column name or * representing all columns.

Return value

The count(*) returns the number of all rows in a group, and count(column_name) return the number of non-null values of the column_name column ​​in a group.

Examples

To demonstrate the usages of count(), we simulate a temporary set with the following UNION statement:

SELECT 'Tim' name, '20220726' login_date
UNION
SELECT 'Tim' name, '20220725' login_date
UNION
SELECT 'Tim' name, '20220724' login_date
UNION
SELECT 'Tom' name, '20220725' login_date
UNION
SELECT 'Tom' name, '20220723' login_date
UNION
SELECT 'Tom' name, NULL login_date;

name  login_date
----  ----------
Tim   20220724
Tim   20220725
Tim   20220726
Tom
Tom   20220723
Tom   20220725

Here, we have some rows for the login history of users, and in each row is a login record for a user. And the last row has a login date of NULL.

If we need to count the number of logins for each person, use the following statement:

SELECT
    t.name,
    count(login_date) login_times
FROM (
    SELECT 'Tim' name, '20220726' login_date
    UNION
    SELECT 'Tim' name, '20220725' login_date
    UNION
    SELECT 'Tim' name, '20220724' login_date
    UNION
    SELECT 'Tom' name, '20220725' login_date
    UNION
    SELECT 'Tom' name, '20220723' login_date
    UNION
    SELECT 'Tom' name, NULL login_date
) t
GROUP BY t.name;

name  login_times
----  -----------
Tim   3
Tom   2

Here, we find that the number of logins for user Tom is 2, which is not correct. The reason is that count(login_date) only counts the number of non-null values in the login_date column. To fix this error, we need to use count(*) instead of count(login_date), as follows:

SELECT
    t.name,
    count(*) login_times
FROM (
    SELECT 'Tim' name, '20220726' login_date
    UNION
    SELECT 'Tim' name, '20220725' login_date
    UNION
    SELECT 'Tim' name, '20220724' login_date
    UNION
    SELECT 'Tom' name, '20220725' login_date
    UNION
    SELECT 'Tom' name, '20220723' login_date
    UNION
    SELECT 'Tom' name, NULL login_date
) t
GROUP BY t.name;

name  login_times
----  -----------
Tim   3
Tom   3

According the statement, SQLite divides all rows into two groups by name first, and count all rows in each group.