How to use Subquery In SQL
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:
Explanation: As we can see, in the subquery we only selected those records which were applicable to the respective count.
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.
Contact Us