Using the PostgreSQL COUNT Command 760

Introduction

When you’re querying a database, there are times when you don’t necessarily need the data in the rows themselves, but you need to know how many rows in a table meet certain criteria. PostgreSQL makes queries like these simple with the COUNT command, an aggregate function that returns the number of rows in a table that meet your specified criteria. In this article, we’ll discuss the PostgreSQL COUNT() command and provide some examples of its use.

Prerequisites

If you’d like to follow along with the examples in this tutorial, you’ll need to make sure that PostgreSQL server is installed and configured on your machine. The service will need to be running in the background.

Linux and Windows users can download PostgreSQL here

What is the PostgreSQL COUNT Command?

The PostgreSQL COUNT() command is an aggregate function. It retrieves the number of rows that satisfy specified criteria stated within the query.

There are numerous ways to use the PostgreSQL COUNT() command. Let’s look at the following statements:

COUNT(column)

The COUNT(column) command will return the number of columns that match the query in the SELECT statement. This statement will not accept NULL as a value of column.

The above code will count the number of column target_column within the table your_table_name where additional_conditions are met.

COUNT (DISTINCT column)

In this version of the COUNT() function, COUNT(DISTINCT column) will retrieve the number of unique values within the column where condition is met:

COUNT(*)

The PostgreSQL COUNT(*) uses the asterisk * character as a wildcard. It instructs PostgreSQL to retrieve all the rows within the target table, including NULL and duplicate values.

Keep in mind that using the * character against a larger table can be a slow process; this is due to the Multiversion Concurrency Control implementation within PostgreSQL. Since there is no other way for the PostgreSQL COUNT(*) command to count data in the table, it must check all the rows.

Creating a Sample Data Set

Let’s create a sample data set that we can use in our examples. In the statement shown below, we create a table named typhoon_info:

Now we can insert records using the following statement:

An Example Using PostgreSQL COUNT(*)

In the following statement, we use the COUNT(*) command to retrieve the number of rows in the table typhoon_info:

The results should look something like the following:

An Example Using PostgreSQL COUNT(DISTINCT column)

In this example, we’ll limit our query to only retrieve distinct values within the specified column in the table typhoon_info. We can do this using the following statement:

We’ll get results that look like this:

An Example Using PostgreSQL COUNT() Combined with the GROUP BY Clause

In our next example, we’ll try to get the number of typhoons by category. We’ll do this by using the clause GROUP BY, which will group the typhoons based on their “strength” category ty_category. To count them, we’ll use the PostgreSQL COUNT() command:

The result of this SELECT statement should look something like this:

We can see that PostgreSQL grouped the typhoons by their respective strength category and counted how many typhoons were in each category.

Conclusion

When you need to know how many rows in your database match a certain set of criteria, but you don’t need the information in the rows themselves, it can be helpful to use an aggregate function. The COUNT command is used in PostgreSQL to provide this functionality. In this article, we discussed many applications of the COUNT command and looked at some real-life examples. After reading this tutorial and following along with the examples, you’ll be able to utilize the COUNT function in your own database queries.

Just the Code

We’ve gathered all the code snippets used in our examples and included them below. You can copy and modify them to fit your own table structure and use case: