PostgreSQL count() Function

The PostgreSQL count() function is an aggregate function that counts and returns the number of all specified values ​​in a group.

count() Syntax

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

count(expr)

Typically, we use the count() function like:

SELECT count(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

Parameters

expr

Required. A column name or * representing all columns.

Return value

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

count() Examples

To demonstrate usages of the PostgreSQL count() function, we simulate a temporary table using the following statement with UNION and SELECT:

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  | 20220726
 Tom  | 20220725
 Tim  | 20220724
 Tim  | 20220725
 Tom  | 20220723
 Tom  | <null>
(6 rows)

Here, we have some rows about the user’s login history, with the user’s name in the name column and the date of a user’s login in the login_date column.

The login date of the last row is NULL.

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
------+-------------
 Tom  |           2
 Tim  |           3
(2 rows)

Here, we find that Tom has logged in 2 times, which is not correct. The reason for this result is that count(login_date) only count the number of non-null values ​​in the login_date column. To fix this error, we need to use count(*), 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
------+-------------
 Tom  |           3
 Tim  |           3
(2 rows)