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:
1 2 3 4 5 6 | SELECT COUNT(<*>,<column>,<DISTINCT column>) FROM <tableName> WHERE <condition>; |
Let’s look at this statement line by line and discuss what’s going on:
First, we specify the
COUNT()
function immediately after theSELECT
statement. TheCOUNT()
function can take arguments such as:*
.column
, orDISTINCT 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:
1 2 3 4 5 | Server [localhost]: ingress.w98sujpz.launchpad.objectrocket.cloud DATABASE [PostgreSQL]: <your_database_name> Port [5432]: 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:
1 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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 (15 rows) |
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:
1 2 3 4 5 | sandbox=> SELECT COUNT(*) FROM student; COUNT ------- 15 (1 ROW) |
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:
1 | SELECT COUNT (DISTINCT grade_level) FROM student; |
The result should look like this:
1 2 3 4 | count ------- 4 (1 row) |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started