CODEWITHSUNDEEP
sqlpostgresql-9.6
Ruslan Muhamadiarov
Ruslan Muhamadiarov

Reputation: 63

Postgres aggregate with case

I have a some query that returns a table "code_1"(id - distinct).

|---------------------|------------------|
|         id          |       status     |
|---------------------|------------------|
|          1          |         true     |
|---------------------|------------------|
|          2          |         true     |
|---------------------|------------------|
|          3          |         false    |
|---------------------|------------------|
|          3          |         true     |
|---------------------|------------------|
|          4          |         false    |
|---------------------|------------------|

Based on the data .. I want to get the following table ("code_2")

|---------------------|------------------|
|     id              |     status       |
|---------------------|------------------|
|          1          |      include     |
|---------------------|------------------|
|          2          |      include     |
|---------------------|------------------|
|          3          |      partial     |
|---------------------|------------------|
|          4          |      exclude     |
|---------------------|------------------|

if the id is repeated status is partial, else status = status from code_1 table

Upvotes: 2

Views: 1351

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

If I understand correctly, use aggregation:

select id,
       (case when min(status) = max(status) and min(status) then 'Include'
             when min(status) = max(status) and not min(status) then 'Exclude'
             else 'Partial'
        end) as status
from t
group by id;

Or, using boolean aggregation functions:

select id,
       (case when bool_and(status) then 'Include'
             when bool_or(status) then 'Partial'
             else 'Exclude'
        end) as status
from t
group by id;

Upvotes: 3

Related Questions