Introduction to PostgreSQL COUNT() function

TheCOUNT() 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 theCOUNT() function.

COUNT(*)

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

SELECT   COUNT(*)FROM   table_nameWHERE   condition;

When you apply theCOUNT(*) function to the entire table, PostgreSQL has to scan the whole table sequentially. If you use theCOUNT(*) 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 forCOUNT(*) function to count across the entire table. Therefore, PostgreSQL must scan all rows.

COUNT(column)

Similar to theCOUNT(*) function, theCOUNT(column_name) function returns the number of rows returned by aSELECT clause. However, it does not considerNULL values in thecolumn_name.

SELECT   COUNT(column_name)FROM   table_nameWHERE   condition;

COUNT(DISTINCT column)

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

SELECT   COUNT(DISTINCT column_name)FROM   table_nameWHERE   condition;

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

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

PostgreSQL COUNT() function examples

Let’s use thepayment table in thesample database for the demonstration.

1) Basic PostgreSQL COUNT(*) example

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

SELECT   COUNT(*)FROM   payment;

Output:

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

2) PostgreSQL COUNT(DISTINCT column) example

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

SELECT  COUNT (DISTINCTamount)FROM  payment;

Output:

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

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

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

SELECT  customer_id,  COUNT (customer_id)FROM  paymentGROUP BY  customer_id;

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  paymentINNER JOIN customer USING (customer_id)GROUP BY  customer_id;

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 theCOUNT 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  paymentINNER JOIN customer USING (customer_id)GROUP BY  customer_idHAVING  COUNT (customer_id)> 40

Output:

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

Summary

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

Last updated onJanuary 26, 2024

On this page

• Introduction to PostgreSQL COUNT() function
• PostgreSQL COUNT() function examples
• Summary