PostgreSQL COUNT Function

Summary: in this tutorial, you will learn how to use the PostgreSQL COUNT() function to count the number of rows in a table.

Introduction to PostgreSQL COUNT() function

The COUNT() function is an aggregate function that allows you to obtain the number of rows that match a specific condition.

The following statement illustrates various ways of using the COUNT() function.

COUNT(*)

The COUNT(*) function returns the number of rows returned by a  SELECT statement, including NULL and duplicates.

SELECT 
   COUNT(*) 
FROM 
   table_name
WHERE
   condition;Code language: SQL (Structured Query Language) (sql)

When you apply the COUNT(*) function to the entire table, PostgreSQL has to scan the whole table sequentially. If you use the COUNT(*) function on a big table, the query will be slow. This is related to the PostgreSQL MVCC implementation.

Due to multiple transactions seeing different states of data simultaneously, there is no direct way for COUNT(*) function to count across the entire table. Therefore, PostgreSQL must scan all rows.

COUNT(column)

Similar to the COUNT(*) function, the COUNT(column_name) function returns the number of rows returned by a SELECT clause. However, it does not consider NULL values in the column_name.

SELECT 
   COUNT(column_name) 
FROM 
   table_name
WHERE
   condition;Code language: SQL (Structured Query Language) (sql)

COUNT(DISTINCT column)

In this syntax, the COUNT(DISTINCT column_name) returns the number of unique non-null values in the column_name.

SELECT 
   COUNT(DISTINCT column_name) 
FROM 
   table_name
WHERE
   condition;Code language: SQL (Structured Query Language) (sql)

In practice, you often use the COUNT() function with the GROUP BY clause to return the number of items for each group.

For example, you can use the COUNT() with the GROUP BY clause to return the number of films in each film category.

PostgreSQL COUNT() function examples

Let’s use the payment table in the sample database for the demonstration.

1) Basic PostgreSQL COUNT(*) example

The following statement uses the COUNT(*) function to return the number of transactions in the payment table:

SELECT
   COUNT(*)
FROM
   payment;Code language: SQL (Structured Query Language) (sql)

Output:

 count
-------
 14596
(1 row)

2) PostgreSQL COUNT(DISTINCT column) example

To get the distinct amounts that customers paid, you use the COUNT(DISTINCT amount) function as shown in the following example:

SELECT 
  COUNT (DISTINCT amount) 
FROM 
  payment;Code language: SQL (Structured Query Language) (sql)

Output:

 count
-------
    19
(1 row)

3) Using PostgreSQL COUNT() function with GROUP BY clause example

The following example uses the COUNT() function with the GROUP BY function to return the number of payments of each customer:

SELECT 
  customer_id, 
  COUNT (customer_id) 
FROM 
  payment 
GROUP BY 
  customer_id;Code language: SQL (Structured Query Language) (sql)

Output:

 customer_id | count
-------------+-------
         184 |    20
          87 |    28
         477 |    21
         273 |    28
...

If you want to display the customer name instead of id, you can join the payment table with the customer table:

SELECT 
  first_name || ' ' || last_name full_name, 
  COUNT (customer_id) 
FROM 
  payment 
INNER JOIN customer USING (customer_id)
GROUP BY 
  customer_id;Code language: JavaScript (javascript)

Output:

       full_name       | count
-----------------------+-------
 Vivian Ruiz           |    20
 Wanda Patterson       |    28
 Dan Paine             |    21
 Priscilla Lowe        |    28
...

4) Using PostgreSQL COUNT() function with HAVING clause

You can use the COUNT function in a  HAVING clause to apply a specific condition to groups. For example, the following statement finds customers who have made over 40 payments:

SELECT 
  first_name || ' ' || last_name full_name, 
  COUNT (customer_id) 
FROM 
  payment 
INNER JOIN customer USING (customer_id)
GROUP BY 
  customer_id
HAVING 
  COUNT (customer_id) > 40Code language: SQL (Structured Query Language) (sql)

Output:

  full_name   | count
--------------+-------
 Karl Seal    |    42
 Eleanor Hunt |    45
(2 rows)

Summary

• Use the PostgreSQL COUNT() function to return the number of rows in a table.