sql - Group query in subquery to get column value as column name

Question

The data i've in my database:

| id| some_id|  status|
|  1| 1      | SUCCESS|
|  2| 2      | SUCCESS| 
|  3| 1      | SUCCESS| 
|  4| 3      | SUCCESS| 
|  5| 1      | SUCCESS| 
|  6| 4      | FAILED | 
|  7| 1      | SUCCESS|
|  8| 1      | FAILED |
|  9| 4      | FAILED |
| 10| 1      | FAILED |
....... 

I ran a query to group by id and status to get the below result:

| some_id| count|  status|
| 1      |    20| SUCCESS| 
| 2      |    5 | SUCCESS| 
| 3      |    10| SUCCESS| 
| 2      |    15| FAILED | 
| 3      |    12| FAILED | 
| 4      |   25 | FAILED | 

I want to use the above query as subquery to get the result below, where the distinct status are column name.

| some_id| SUCCESS|  FAILED|
| 1      |    20  |  null/0| 
| 2      |    5   |  15    | 
| 3      |    10  |  12    | 
| 4      |  null/0|  25    | 

Any other approach to get the final data is also appreciated. Let me know if need more info.

Thanks

question from:https://stackoverflow.com/questions/65713896/group-query-in-subquery-to-get-column-value-as-column-name

Answer 1

You may use a pivot query here with the help of FILTER:

SELECT
    some_id,
    COUNT(*) FILTER (WHERE status = 'SUCCESS') AS SUCCESS,
    COUNT(*) FILTER (WHERE status = 'FAILED')  AS FAILED
FROM yourTable
GROUP BY
    some_id;

Demo