How to Get Multiple Counts With Single Query in PostgreSQL?

Efficient data analysis often requires counting occurrences of different categories within a dataset. PostgreSQL, a powerful relational database management system offers a feature that allows us to achieve this efficiently.

In this article, we’ll explore how to Get Multiple Counts With a Single Query using various methods along with examples and so on.

How to Get Multiple Counts With a Single Query?

When analyzing data, it is common to need counts of different categories within a dataset. For example, we might want to count the number of orders by region, the number of products in each category or the number of users in each age group.

Manually querying each count separately can be time-consuming and inefficient. Below are the methods that are used to get multiple counts with a single query in PostgreSQL.

1. Using CASE Statements and GROUP BY Clause
2. Using Subquery

Let’s set up an Environment to Get Multiple Counts With Single Query

To understand How to Get Multiple Counts With Single Query in PostgreSQL we need a table on which we will perform various operations and queries. Here we will consider a table called test which contains id val1 and val2 as Columns.

CREATE TABLE test (
  id INT PRIMARY KEY,
  val1 VARCHAR(50),
  val2 VARCHAR(50)
);

INSERT INTO test VALUES (1, 'type1', 'red');
INSERT INTO test VALUES (2, 'type2', 'red');
INSERT INTO test VALUES (3, 'type3', 'blue');
INSERT INTO test VALUES (4, 'type1', 'green');
INSERT INTO test VALUES (5, 'type2', 'red');
INSERT INTO test VALUES (6, 'type3', 'blue');
INSERT INTO test VALUES (7, 'type1', 'green');
INSERT INTO test VALUES (8, 'type2', 'green');
INSERT INTO test VALUES (9, 'type3', 'red');
INSERT INTO test VALUES (10, 'type1', 'blue');
INSERT INTO test VALUES (11, 'type2', 'red');
INSERT INTO test VALUES (12, 'type3', 'green');
INSERT INTO test VALUES (13, 'type1', 'red');
INSERT INTO test VALUES (14, 'type2', 'green');
INSERT INTO test VALUES (15, 'type3', 'red');

Output:

Output

Explanation: Our table has been created.

1. Using CASE Statements and GROUP BY Clause

Our problem is to analyze the count of occurrences of different values (red, blue, green) in the val2 column for each distinct value in the val1 column in the test table. The query calculates these counts using SUM() function with CASE statements for each value, groups the results by val1, and orders them by val1.

SELECT val1,
  SUM(CASE WHEN val2='red' THEN 1 ELSE 0 END) AS red_cnt,
  SUM(CASE WHEN val2='blue' THEN 1 ELSE 0 END) AS blue_cnt,
  SUM(CASE WHEN val2='green' THEN 1 ELSE 0 END) AS green_cnt
FROM test
GROUP BY val1
ORDER BY val1;

Output:

Output

Explanation: As we can see, using the CASE statements we were able to get multiple counts.

2. Using Subquery

The query aims to count the occurrences of different values (‘red‘, ‘blue‘, ‘green‘) in the val2 column for each distinct value in the val1 column in the test table. It uses subqueries to calculate the counts for each color category, grouped by val1, and orders the results by val1.

SELECT t.val1,
  (SELECT COUNT(*) from test WHERE val2='red' and val1=t.val1) AS red_cnt,
  (SELECT COUNT(*) from test WHERE val2='blue' and val1=t.val1) AS blue_cnt,
  (SELECT COUNT(*) from test WHERE val2='green' and val1=t.val1) AS green_cnt
FROM (SELECT DISTINCT val1 FROM test) t
ORDER BY t.val1;

Output:

colour count

Explanation: As we can see, in the subquery we only selected those records which were applicable to the respective count.

Conclusion

In this article, we first started by looking at what CASE statements are and understood how we can use CASE statements. We later, used the SUM() function and CASE statements to find multiple counts in a single query. Finally, we went through an advanced example to solidify our understanding.