How to use CASE Statements and GROUP BY Clause In SQL
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.
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