COUNT in Postgres

Introduction

When you’re querying data stored in PostgreSQL, there may be times when you need the number of records that would be returned from a given query, rather than the content of the rows themselves. Fortunately, you can use COUNT in Postgres to accomplish this task. The COUNT() function is an aggregate function that simply returns the number of rows expected to match the query criteria. In this article, we’ll take a closer look at the PostgreSQL COUNT() function and check out some examples of the function’s use.

Prerequisite

In order to get the most out of this tutorial, you’ll need to have PostgreSQL installed and configured on your system.

What is Postgres COUNT()?

Let’s begin our tutorial with a basic overview of the function COUNT in Postgres. The COUNT() function is a PostgreSQL aggregate function that lets us retrieve the number of rows that match the specified condition within a query.

Shown below is the basic syntax for the COUNT() function:

1
2
3
4
5
6
SELECT
   COUNT(*)
FROM
   <table_name>
WHERE
   <your_condition>;

The COUNT(*) function returns the total number of rows that would be included in the result set returned by the SELECT statement. This total includes duplicates and NULL values.

Create Sample Data set

In this section, we’ll create a sample dataset that we can use in this tutorial.

First, let’s create a table with the structure shown below:

1
2
3
4
5
6
CREATE TABLE worke (
    id INT PRIMARY KEY,
    first_name VARCHAR (20),
    last_name VARCHAR (20),
    department VARCHAR (30)
);

Then we’ll insert the following records:

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO worker (id, first_name, last_name, department)
VALUES
    (1, 'heidi', 'ong', 'ict'),
    (2, 'jason', 'drilon', 'engineering'),
    (3, 'james', 'pangilinan', 'engineering'),
    (4, 'nadine', 'tandingan', 'operations'),
    (5, 'luke', 'pomoy', 'operations'),
    (6, 'jenna', 'geronimo', 'ict'),
    (7, 'rosy', 'garner', 'operations'),
    (8, 'marlon', 'looney', 'payroll'),
    (9, 'dantes', 'prenson', 'payroll'),
    (10, 'rupert', 'bander', 'admin');

At this point, our table should look something like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
id | first_name | last_name  | department
----+------------+------------+-------------
1  | heidi      | ong        | ict
2  | jason      | drilon     | engineering
3  | james      | pangilinan | engineering
4  | nadine     | tandingan  | operations
5  | luke       | pomoy      | operations
6  | jenna      | geronimo   | ict
7  | rosy       | garner     | operations
8  | marlon     | looney     | payroll
9  | dantes     | prenson    | payroll
10 | rupert     | bander     | admin
(10 rows)

Postgres COUNT() Example

Now that we’ve covered the basics of the Postgres COUNT() function, we can look at some examples of how it works.

Let’s count the number of records in our sample dataset by using the following query:

1
2
3
4
SELECT
    COUNT(*)
FROM
    employee;

The output will look something like this:

1
2
3
4
 count
-------
    10
(1 row)

Postgres COUNT() with DISTINCT example

In the previous section, we used the COUNT() function in its most basic form. Now we’ll try something a bit more complex– we’ll be using the COUNT() function within a SELECT statement combined with the DISTINCT clause.

The DISTINCT clause maintains a single row for each group of duplicates. An example of the clause is shown below:

1
2
3
4
SELECT
   COUNT (DISTINCT department)
FROM
   employee;

The output should look something like this:

1
2
3
4
 count
-------
     5
(1 row)

In this query, we counted the records in the ‘worker’ table to satisfy the COUNT() function. The DISTINCT() clause came into play by tracking how many unique ‘departments’ there were and counting groups of duplicates as one.

Let’s study the table shown below:

1
2
3
4
5
6
7
8
9
10
11
12
13
id | first_name | last_name  | department
----+------------+------------+-------------
1  | heidi      | ong        | ict
2  | jason      | drilon     | engineering
3  | james      | pangilinan | engineering
4  | nadine     | tandingan  | operations
5  | luke       | pomoy      | operations
6  | jenna      | geronimo   | ict
7  | rosy       | garner     | operations
8  | marlon     | looney     | payroll
9  | dantes     | prenson    | payroll
10 | rupert     | bander     | admin
(10 rows)

Notice that we have 10 rows in this table; however, our query instructed Postgres to perform the COUNT() function while using the DISTINCT() clause to eliminate duplicates in the ‘department’ column. If we look closely at the values in the ‘department’ column, we can see that the number of distinct departments is indeed five.

Conclusion

When you need to know how many rows would be returned from a SELECT statement in PostgreSQL, the COUNT() function can be used to get the information you need. In this article, we looked at a few different applications of COUNT in Postgres. With these helpful examples to guide you, you’ll be prepared to use aggregate functions like COUNT() in your own PostgreSQL queries.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.