PostgreSQL – COUNT() Function
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:
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:
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:
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 JOIN operations to count rows that meet certain join conditions across multiple tables.
