PostgreSQL COUNT() Function (original) (raw)

Last Updated : 15 Jul, 2025

The **COUNT() function in **PostgreSQL is an aggregate function used to return the number of rows that match a specified condition in a query. This article will explore the various syntaxes of the COUNT() function and provide practical examples to help you understand its usage in **PostgreSQL.

Syntax

Depending upon the user requirements the COUNT() function can have the following syntaxes:

*Syntax: COUNT()

**Returns: All rows including **NULL and **Duplicates.

**Syntax: COUNT(column)

**Returns: All rows except NULL.

**Syntax: COUNT(DISTINCT column)

**Returns: All rows without **NULL and **Duplicates.

**Note: The COUNT() function is used with the SELECT statement.

PostgreSQL COUNT() Function Examples

Let us look into some of the examples of COUNT() Function in PostgreSQL to better understand the concept. For examples we will be using the sample database (ie, dvdrental).

**Example 1: Counting All Rows in a Table

In this example we will use the **COUNT(*) function to get the number of transactions in the '**payment' table using the command below.

**Query:

*SELECT COUNT() **FROM payment;

**Output:

PostgreSQL COUNT() Function Example

**Explanation: The output will display the total number of rows in the '**payment' table.

**Example 2: Counting Distinct Values in a Column

In this example we will query for the distinct amounts which customers paid, using the '**COUNT(DISTINCT column)' function as shown below.

**Query:

**SELECT COUNT (DISTINCT amount) **FROM payment;

**Output:

PostgreSQL COUNT() Function Example

**Explanation: The output will display the number of unique payment amounts.

**Example 3: Counting Rows with a Condition

Here we will be using the COUNT() function to get the details of customers who have made more than 40 payments.

**Query:

**SELECT customer_id, COUNT (customer_id) FROM payment **GROUP BY customer_id **HAVING **COUNT (customer_id) > 40;

**Output:

PostgreSQL COUNT() Function Example

**Explanation: The output will display the '**customer_id' and the count of payments for customers who have made more than 40 payments.

Important Points About PostgreSQL COUNT() Function

The '**COUNT(column)' function ignores **NULL values. This is useful for counting non-empty entries in a column.

****'COUNT(*)'** is generally faster than '**COUNT(column)' because it does not need to check for **NULL values in a specific column.

The **COUNT() function returns **zero when no rows match the condition, but returns **NULL if there are no rows to count at all, depending on the context of the query.

The **COUNT() function can be used with **JOINoperations to count rows that meet certain join conditions across multiple tables.