Postgres supports various aggregate functions such as COUNT(), AVG(), SUM(), etc. Among them, the most popularly used is the COUNT() function which counts the number of rows of a table. While the FILTER is a handy clause that can be used with the COUNT() function to do multiple counts on a particular table.

This article will teach you how to do multiple counts on a particular table using the FILTER clause.

How Do I Utilize the FILTER Clause to Do Multiple Counts in Postgres?

In PostgreSQL, users can utilize the following syntax to do multiple counts on a specific table:

SELECT COUNT(1),
COUNT(1) FILTER (criteria_1),
COUNT(1) FILTER (criteria_2),
…
FROM tab_name;

The COUNT() function will count the table rows based on the specified criteria.

Example: How to Use FILTER Clause With the COUNT() Function?

In this example, we will utilize a sample table named “author_info” that we have already created with the following records:

SELECT * FROM author_info
ORDER BY author_id;

Suppose we want to do multiple counts on the “author_info” table. To do that, we will utilize the provided query:

SELECT COUNT(1) AS total_authors,
COUNT(1) FILTER (
WHERE gender = 'M') AS male_authors,
COUNT(1) FILTER (
WHERE gender = 'F') AS female_authors
FROM author_info;

Here in the above query:

- The first COUNT() will count all authors from the given table.
- The second count will count only male authors.
- The final COUNT() will count only female authors.

That was all about using the FILTER clause to have multiple counts in Postgres.

Conclusion

In PostgreSQL, the FILTER clause can be used with the COUNT() function to do multiple counts on a particular table. The COUNT() function will count the table rows based on the specified criteria. This article has explained the working of the COUNT() function along with the FILTER clause to do multiple counts on a specific Postgres table.