PostgreSQL COUNT() Function: Syntax & Example Queries Explained

PostgreSQL is one of the most robust Relational Database Management Systems. You can use SQL to query your data stored in the database. There might be instances where there would be a need to count the number of records in a table or sometimes a specific number of rows according to a condition.

This is where you can use the COUNT() function in PostgreSQL. In this article, you will come to know about the PostgreSQL COUNT function along with a wide range of use cases and examples. Let’s get started!

PostgreSQL COUNT() Function: Overview

The different ways of using the PostgreSQL COUNT() function are as follows:

• COUNT(*)
• COUNT(Column_name)
• COUNT(DISTINCT Column_name)

1) COUNT(*)

You can use the PostgreSQL COUNT(*) function along with a SELECT statement to return the total number of rows in a table including the NULL values as well as the duplicates.

It can be implemented in the following format.

SELECT 
   COUNT(*) 
FROM 
   table_name
WHERE
   condition;

In this case, the COUNT(*) function is applied to the entire table i.e, table_name table. And this function will count the number of rows according to the condition applied along with the WHERE clause.

2) COUNT(Column_name)

You can use the COUNT(Column_name) function along with a SELECT statement to return the total number of non-NULL values in the Column_name column.

It can be implemented in the following format.

SELECT 
   COUNT(column_name) 
FROM 
   table_name
WHERE
   condition;

In this case, the COUNT(column_name) function is applied to the column_name column. And this function will count the number of rows according to the condition applied along with the WHERE clause.

3) COUNT(DISTINCT Column_name)

You can use the COUNT(DISTINCT Column_name) function along with a SELECT statement to return the total number of unique, non-NULL values in the Column_name column.

It can be implemented in the following format.

SELECT 
   COUNT(DISTINCT column_name) 
FROM 
   table_name
WHERE
   condition;

In this case, the COUNT(DISTINCT column_name) function is applied to the column_name column. And this function will count the number of rows according to the condition applied along with the WHERE clause.

PostgreSQL COUNT() Function: Example Queries

For showcasing different example queries using the PostgreSQL COUNT() function, we are considering this table with the following fields.

The different use cases of the PostgreSQL COUNT() function can be illustrated as follows:

• COUNT(*)
• COUNT() on Specific Column
• COUNT() DISTINCT
• COUNT() with GROUP BY clause
• COUNT() with WHERE clause
• COUNT() with HAVING clause
• COUNT() with ORDER BY & GROUP BY clauses

1) COUNT(*)

SELECT COUNT(*) 
FROM Employees;

This function will return the total number of records in the Employees table.

2) COUNT() on Specific Column

SELECT COUNT(commission) 
FROM Employees;

The PostgreSQL COUNT() function is applied to the commission column. It will return the total count of the non-null values in the commission column i.e., it will give the count of the employees who only earn a commission.

3) COUNT() DISTINCT

SELECT COUNT(DISTINCT designation) 
FROM Employees;

The PostgreSQL COUNT() function is applied to the commission column along with the DISTINCT clause. It will return the total count of the unique values of designation.

4) COUNT() with GROUP BY clause

SELECT
	dept_no,
	COUNT (*) AS "Number of Employees"
FROM
	Employees
GROUP BY
	dept_no;

The PostgreSQL COUNT() function is used here to count the total number of employees in each department. Hence, GROUP By clause is applied to the dept_no column.

5) COUNT() with WHERE clause

SELECT
	dept_no,
	COUNT (*) AS "Number of Employees"
FROM
	Employees
WHERE 
       Salary > 50000
GROUP BY
	dept_no;

The PostgreSQL COUNT() function is used here to count the total number of employees in each department. Hence, GROUP By clause is applied to the dept_no column. Along with that, the WHERE clause is used to set a condition, it will only count the number of employees in each department whose salary is greater than $50,000.

6) COUNT() with HAVING clause

SELECT
	dept_no,
	COUNT (*) AS "Number of Employees"
FROM
	Employees
GROUP BY
	dept_no;
ORDER BY
        COUNT(*) DESC;

The PostgreSQL COUNT() function is used here to count the total number of employees in each department. Hence, GROUP By clause is applied to the dept_no column. And it will only display the total count of employees in those departments where the employee count is greater than 40.

The PostgreSQL COUNT() function is used here to count the total number of employees in each department. Hence, GROUP By clause is applied to the dept_no column. And it will only display the output in the descending order of the number of employees in each department.

7) COUNT() with ORDER BY & GROUP BY clauses

SELECT
	dept_no,
	COUNT (*) AS "Number of Employees"
FROM
	Employees
GROUP BY
	dept_no
HAVING
        COUNT(*)> 40;

For further information on the COUNT() function in PostgreSQL, you can visit the official documentation.

Before wrapping up, let’s cover some basics as well.

What is PostgreSQL COUNT() Function?

The COUNT() function in PostgreSQL is an aggregate function that counts the number of rows or non-NULL values against a specified column or an entire table. It can also be used to return the number of rows that match a given query criterion.

1) Syntax

COUNT (* | [DISTINCT] ALL | column_name)

2) Parameters

• column_name: This represents the name of the column for which you want to count non-NULL values of the records.
• *: This represents the total number of rows for all the fields.
• DISTINCT: It indicates the number of unique values for any particular column or all the values. It is an optional clause.
• ALL: It is a default clause. And it’s optional.

You can also take a look how you can work with PostgreSQL Subquery and the PostgreSQL SELECT queries to get a better understanding of PostgreSQL Queries.

Conclusion

This article illustrated the usage of the COUNT() function in PostgreSQL. You had an in-depth understanding of the different syntax and use cases of the COUNT() function. Now, you can move forward and use the COUNT() function to count the number of rows in a table according to your requirements.

It will take a lot of time to import and move data into your selected warehouse by using ETL for Data Analysis with PostgreSQL as your data source. However, with Hevo at your fingertips, you won’t have to worry about your PostgreSQL Data Replication demands. It will just take a few minutes to load your data into your chosen data warehouse.

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources, the platform also helps you to transform & enrich your data, & make it analysis-ready. 

Sign up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.

Frequently Asked Questions