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:
1 | SELECT COUNT ( [*], [DISTINCT] [column_name] ) FROM TABLE_NAME; |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | country_id | country_name | continent | code ------------+--------------------------+---------------+------ 35 | South Africa | Africa | AF 14 | Canada | North America | NA 51 | Cuba | North America | NA 41 | Mexico | North America | NA 45 | United States OF America | North America | NA 85 | Argentina | South America | SA 37 | Venezuela | South America | SA 63 | Philippines | Asia | AS 83 | China | Asia | AS 22 | Russia | Asia | AS 21 | United Arab Emirates | Asia | AS 62 | Germany | Europe | EU 73 | Italy | Europe | EU 28 | United Kingdom | Europe | EU |
SELECT COUNT all example
For our first example, we’ll count the number of rows in the table:
1 | SELECT COUNT(*) AS number_of_rows FROM countries; |
The result of this query will look like the following:
1 2 3 | number_of_rows ---------------- 14 |
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.
1 | SELECT COUNT("country_name") AS number_of_rows FROM countries; |
The result of this query can be seen below:
1 2 3 | number_of_rows ---------------- 14 |
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”:
1 | SELECT COUNT( DISTINCT continent ) FROM countries; |
The result of this query will be a little different:
1 2 3 | COUNT ------- 5 |
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”:
1 2 3 | SELECT code, COUNT(code) FROM countries GROUP BY code; |
Here’s the result of our query:
1 2 3 4 5 6 7 | code | COUNT ------+------- NA | 4 SA | 2 AF | 1 AS | 4 EU | 3 |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started