PostgreSQL COUNT Function: A 101 Guide

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.

Table of Contents

• What is PostgreSQL COUNT() Function?
• PostgreSQL COUNT Function: Overview
• PostgreSQL COUNT Function: Example Queries
• Conclusion

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.

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.

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;

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.

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

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.

Want to explore more about different clauses and statements while writing queries and creating tables in PostgreSQL? You can go through these articles.

• Working with PostgreSQL Subquery | A 101 Guide
• PostgreSQL SELECT Statement: Syntax & Example Queries | A 101 Guide

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. When you consider how much money and resources are required to engage data engineers and analysts to make sense of this data, the issue becomes even more daunting.

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’s strong integration with 150+ Data Sources (including 40+ Free Sources) like PostgreSQL, you can export data from your selected data sources and load it to the destination of your choice. It also assists you with reorganizing and enhancing your data so that it is ready for analysis. Now, you can readily save your time and focus on gaining insights and doing in-depth research on your data using BI solutions.

Visit our Website to Explore Hevo

You can now replicate your PostgreSQL data to any data warehouse of your choice, including Amazon Redshift, Snowflake, Google BigQuery, and Firebolt.

Why don’t you give Hevo a try? Sign Up here for a 14-day full feature access trial and experience the feature-rich Hevo suite first hand. You can also check our unbeatable pricing and make a decision on your best-suited plan. 

Share your thoughts on learning about the PostgreSQL COUNT() function in the comments section below. If you have any questions, do let us know. We’d be happy to help.