Postgres aggregate with case

Question

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

Gordon Linoff · Accepted Answer · 2020-06-28 12:15:21Z

4

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;

answered Jun 28, 2020 at 12:15

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres aggregate with case

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related