Combining SQL FULL JOINS-CodePudding

Good day!

I wanted to join tables where it would result to this:

 ------- ----------- -------- 
| area  | confirmed | active |
 ------- ----------- -------- 
| area1 | 2         | 0      |
 ------- ----------- -------- 
| area2 | 1         | 1      |
 ------- ----------- -------- 
| area3 | 0         | 0      |
 ------- ----------- --------

from a single table that contains this:

 ---- ------- ----------- 
| id | area  | status    |
 ---- ------- ----------- 
| 1  | area1 | confirmed |
 ---- ------- ----------- 
| 2  | area1 | confirmed |
 ---- ------- ----------- 
| 3  | area2 | active    |
 ---- ------- ----------- 
| 4  | area2 | confirmed |
 ---- ------- ----------- 
| 5  | area3 | inactive  |
 ---- ------- -----------

What I've tried is to full join respective "selects" to act as tables to be used in the FROM clause but that was not the result that I wanted. I don't know if this is even possible or should I just manipulate the data upon fetching with the sub-selects.

Does anyone have any idea? Thanks!

CodePudding user response：

This is a simple case of conditional aggregation, for which you can use the FILTER clause in PostgreSQL

SELECT
  area,
  COUNT(*) FILTER (WHERE status = 'confirmed') AS confirmed,
  COUNT(*) FILTER (WHERE status = 'active'   ) AS active
FROM YourTable
GROUP BY area

In other RDBMSs, you can simulate FILTER with COUNT(CASE WHEN