How to Use the Postgres Count Select

Introduction

In PostgreSQL, the COUNT() function returns the number of rows in a specified table, and a SELECT statement returns records that match the specified query conditions. Used together, this function and statement can take your PostgreSQL queries to the next level and return the number of records that meet the criteria specified in the query. In this article, we’ll show how to use Postgres COUNT and SELECT in tandem to find out how many records are returned from a given query.

Prerequisites

Before you go any further with this tutorial, make sure that PostgreSQL is installed on your computer. You’ll need to use the database to perform your SELECT COUNT statements. You’ll also need to have some introductory knowledge of PostgreSQL in order to follow along with the examples provided in this article.

PostgreSQL COUNT SELECT

The following syntax is used when the COUNT() function is used in conjunction with a SELECT statement:

Let’s dig a little deeper into the syntax shown above:

• SELECT – This is used to select certain columns from the database.

• COUNT – This is used to count the number of records in this table.

• DISTINCT – This clause is used to eliminate duplicates from the returned records.

• Asterisk (*) – Using the asterisk is equivalent to selecting all the columns in a table.

• column_name – This represents the name of the column for which you want to count the records.

• table_name – This represents the name of the table for which you want to count the records.

PostgreSQL Count Function Examples

We’ll be using a table named countries in our examples:

SELECT COUNT all example

For our first example, we’ll count the number of rows in the table:

The result of this query will look like the following:

Notice that we’ve used an alias by including the AS clause to specify the new name of the column.

The result only includes one column, since the only data being returned is the number of rows in the table.

COUNT column example

Next, let’s try to count the number of rows in a specified column.

The result of this query can be seen below:

The result is the same as the first example since this query also counts the total number of rows in the table.

Count distinct example

Let’s try a more complex example this time. We’ll use the DISTINCT clause and count the number of rows in the column named “continent”:

The result of this query will be a little different:

In this query, the result is only five because the DISTINCT function removes any duplicate values found in the records.

COUNT GROUP BY

Our next example will make use of the GROUP BY clause. We’ll count the number of rows for each value found in the column named “code”:

Here’s the result of our query:

Conclusion

When you’re querying a PostgreSQL table, there are times when you need the number of records that match certain conditions more than you need the actual records themselves. Using the Postgres COUNT and SELECT together can help you get the results you need. In this article, we provided several examples that use the PostgreSQL SELECT statement in conjunction with the COUNT() function. If you follow along with these examples, you’ll be prepared to create similar queries for your own PostgreSQL tables.