How to Use the Postgres COUNT on an ObjectRocket Instance

Introduction

When you execute a PostgreSQL query, your results will include any records that match the conditions defined in the WHERE clause. However, there are times when it’s more important to have the number of non-NULL values in a column that match the query criteria, instead of the values themselves. The PostgreSQL COUNT function makes it easy to retrieve this information and understand your data better. This article will discuss the COUNT() function in further detail and explain how to use Postgres COUNT on an ObjectRocket instance.

Prerequisites

Two key prerequisites need to be in place before proceeding with this tutorial:

• PostgreSQL 11 must be installed on your machine.

• You must create an instance of Postgres for your ObjectRocket account using the Mission Control panel.

PostgreSQL COUNT() Function

The Postgres COUNT() function is an example of an aggregate function. It allows us to retrieve the number of rows that match the given conditions defined in a SQL statement.

We can see the syntax of the COUNT() function below:

Let’s look at this statement line by line and discuss what’s going on:

• First, we specify the COUNT() function immediately after the SELECT statement. The COUNT() function can take arguments such as: *. column, or DISTINCT column. We’ll discuss these arguments in further detail in the next section.

• Next, we specify the name of the table in the FROM clause.

• Finally, we use the WHERE clause to define the conditions that further refine our result set.

Connecting to a PostgreSQL Instance on ObjectRocket

We will be using the following connection details to access PostgreSQL on ObjectRocket.

Connecting Using Postgres Psql

We’ll use the psql interface for PostgreSQL:

Connecting Using Terminal

We can connect to our PostgreSQL instance on ObjectRocket using the terminal. The command we’ll use is shown below:

Sample Data Set

We’ll need some sample data to work with in our examples. The following table will serve the purposes of this tutorial. If you plan to follow along with our examples, it’s a good idea to create similar sample data in your own database:

Postgres COUNT(*) Function Example

In our first example of using the Postgres COUNT on an ObjectRocket instance, we’ll use the function to count the entire student table. Our SELECT statement will look like the following:

We can see that our COUNT() function returned the correct information about the number of rows in the table.

Postgres COUNT(DISTINCT Column) Function Example

Next, let’s look at an example that combines the COUNT function and the DISTINCT clause. The DISTINCT clause will take duplicate rows into consideration, only counting them once:

The result should look like this:

NOTE: If we tried to count how many variations of grade_level exist in the table, we’d come up with the same result.

Conclusion

When you construct a PostgreSQL query, you may want to find out how many records match certain query conditions. With the Postgres COUNT function, it’s easy to get this information. In this article, we explained how the function works and showed how to use Postgres COUNT on an ObjectRocket instance. With our examples as a guide, you’ll have no trouble using the COUNT() function in your own queries.