PostgreSQL – COUNT() Function
The COUNT() function is an aggregate function that enables users to get the number of rows that match a particular requirement of a query.
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
The COUNT() function is used with the SELECT statement.
For examples we will be using the sample database (ie, dvdrental).
Example 1:
In this example we will use the COUNT(*) function to get the number of transactions in the payment table using the command below:
SELECT COUNT(*) FROM payment;
Output:
Example 2:
In this example we will query for the distinct amounts which customers paid, using the COUNT(DISTINCT column) function as shown below:
SELECT COUNT (DISTINCT amount) FROM payment;
Output:
Example 3:
Here we will be using the COUNT() function to get the details of customers who have made more than 40 payments:
SELECT customer_id, COUNT (customer_id) FROM payment GROUP BY customer_id HAVING COUNT (customer_id) > 40;
Output: