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?
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.
As the ability of businesses to collect data explodes, data teams have a crucial role to play in fueling data-driven decisions. Yet, they struggle to consolidate the scattered data in their warehouse to build a single source of truth. Broken pipelines, data quality issues, bugs and errors, and lack of control and visibility over the data flow make data integration a nightmare.
1000+ data teams rely on Hevo’s Data Pipeline Platform to integrate data from over 150+ sources in a matter of minutes. Billions of data events from sources as varied as SaaS apps, Databases, File Storage, and Streaming sources can be replicated in near real-time with Hevo’s fault-tolerant architecture. What’s more – Hevo puts complete control in the hands of data teams with intuitive dashboards for pipeline monitoring, auto-schema management, and custom ingestion/loading schedules.
All of this combined with transparent pricing and 24×7 support makes us the most loved data pipeline software on review sites.
Take our 14-day free trial to experience a better way to manage data pipelines.
Get started for Free with Hevo!
PostgreSQL COUNT() Function: Overview
The different ways of using the PostgreSQL COUNT() function are as follows:
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.
Image Source: Self
The different use cases of the PostgreSQL COUNT() function can be illustrated as follows:
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.
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.