PostgreSQL COUNT Aggregate Function

Summary: In this tutorial, you’ll learn how to use the PostgreSQL COUNT aggregate function to count the number of values in a set.

Getting Started with the PostgreSQL COUNT Aggregate Function #

The PostgreSQL COUNT aggregate function counts the number of values in a set.

Here’s the syntax of the COUNT function:

SELECT
  COUNT(column1)
FROM
  table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the COUNT function returns the number of values in column1 of table_name.

If column1 contains NULL, the COUNT(column1) function does not count NULL. In other words, it ignores NULL in the result.

If column1 has no values, the COUNT(column1) function returns zero (0). Unlike the SUM and AVG functions, the COUNT function does not return NULL if column1 has no values.

COUNT(DISTINCT column1) #

The COUNT function, with its DISTINCT option, empowers you to count distinct values in a column, offering flexibility in your data analysis:

SELECT
  COUNT(DISTINCT column1)
FROM
  table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• If column1 has NULL, the COUNT(DISTINCT column1) will count the NULL.
• If column1 has multiple NULLs, the COUNT(DISTINCT column1) will consider all duplicates and include one NULL in the result.

COUNT(*) #

The COUNT(*) returns the number of values returned by the SELECT statement, including NULL and duplicates:

SELECT
  COUNT(*)
FROM
  table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

COUNT(column1) vs. COUNT(DISTINCT column1) vs. COUNT(*) #

Let’s take an example to understand the difference between COUNT(column1), COUNT(DISTINCT column1), and COUNT(*):

First, create a table t with one column id:

CREATE TABLE t(id INT);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, insert rows into the t table:

INSERT INTO t(id) VALUES(1),(2),(2),(3), (NULL) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

  id
------
    1
    2
    2
    3
 NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, use the COUNT(id), COUNT(DISTINCT id), and COUNT(*) functions:

SELECT
  COUNT(id) AS count_id,
  COUNT(DISTINCT id) AS count_distinct_id,
  COUNT(*) AS count_star
FROM
  t;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 count_id | count_distinct_id | count_star
----------+-------------------+------------
        4 |                 3 |          5Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this result:

• The COUNT(id) returns four because it ignores NULL but includes duplicates.
• The COUNT(DISTINCT id) returns three (1, 2, 3) because it ignores NULL and a duplicate id (2).
• The COUNT(*) returns five because it includes NULL and duplicates.

PostgreSQL COUNT Aggregate Function Examples #

Let’s explore some examples of using the COUNT function.

Counting Rows in a Table #

The following SELECT statement uses the COUNT() function to count the number of products in the products table:

SELECT
  COUNT(*)
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 count
-------
    25Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Counting Values in Groups #

The following SELECT statement uses the COUNT() function to retrieve product count by brand:

SELECT
  brand_id,
  COUNT(product_id)
FROM
  products
GROUP BY
  brand_id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 brand_id | count
----------+-------
        9 |     1
        3 |     1
        5 |     3
        4 |     1
       10 |     1
        6 |     1
        2 |     6
        7 |     4
        1 |     6
        8 |     1Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

• The GROUP BY clause groups the products by brand_id.
• The COUNT function counts the number of products for each group.

If you want to get the brand name instead of id, you can join the products table with the brands table:

SELECT
  brand_name,
  COUNT(product_id)
FROM
  products
  JOIN brands USING (brand_id)
GROUP BY
  brand_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 brand_name | count
------------+-------
 Microsoft  |     1
 HP         |     1
 Sony       |     3
 Huawei     |     1
 Lenovo     |     1
 Samsung    |     6
 LG         |     1
 Dell       |     4
 Xiaomi     |     1
 Apple      |     6Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using COUNT in HAVING Clause #

The following statement gets the brands that have three or more products:

SELECT
  brand_name,
  COUNT(product_id)
FROM
  products
  JOIN brands USING (brand_id)
GROUP BY
  brand_name
HAVING
  COUNT(*) > 3;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 brand_name | count
------------+-------
 Samsung    |     6
 Dell       |     4
 Apple      |     6Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the HAVING clause filters the brands with the number of products more than 3.

Summary #

• Use COUNT(column1) to count the number of non-NULL values in column1, including duplicates.
• Use COUNT(DISTINCT column1) to count the number of distinct values in column1, including NULL.
• Use COUNT(*) to count the number of values, including NULL and duplicates.

Quiz #