How to Use the Postgres COUNT on an ObjectRocket Instance
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.
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
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
COUNT()function can take arguments such as:
DISTINCT column. We’ll discuss these arguments in further detail in the next section.
Next, we specify the name of the table in the
Finally, we use the
WHEREclause 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:
Server [localhost]: ingress.w98sujpz.launchpad.objectrocket.cloud
DATABASE [PostgreSQL]: <your_database_name>
Port : 4149
Username [PostgreSQL]: pguser
Password FOR USER orkb: <your_password>
Connecting Using Terminal
We can connect to our PostgreSQL instance on ObjectRocket using the terminal. The command we’ll use is shown below:
psql -h ingress.w98sujpz.launchpad.objectrocket.cloud -U pguser -d PostgreSQL -p 4149
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:
id | name | grade_level
1 | dex luther | freshman
2 | ronan dune | freshman
3 | james cameron | freshman
4 | abi reign | sophomore
5 | ronald mackay | sophomore
6 | leslie loom | sophomore
7 | heidy stunt | junior
8 | rommel galisanao | junior
9 | daniel wayne | junior
10 | alice mcknott | junior
11 | raizel mendez | senior
12 | yeshua love | senior
13 | daniel jones | senior
14 | aldrin victa | senior
15 | pete potus | senior
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:
sandbox=> SELECT COUNT(*) FROM student;
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:
SELECT COUNT (DISTINCT grade_level) FROM student;
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.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started