user3273269 user3273269 - 1 month ago 12
SQL Question

SQL Custom column based on the values in the column

I have a scenario where I need to show specific status value in new column


for the ID, if any status is converted, show converted
if all status is closed for the ID only then show closed
else show open

i am having trouble in looking in the column to find if all are closed for specific ID or if any is converted, please help how to handle it :

Data :

1 5 new
1 6 closed
1 7 wip
2 22 Converted
2 25 Closed
3 11 closed
3 44 closed

output i want to get

ID status
1 open
2 Converted
3 Closed

Answer Source

This is just a matter of conditions and aggregation. Here is one method:

select id,
       (case when sum(case when status = 'converted' then 1 else 0 end) > 0
             then 'converted'
             when min(status) = max(status) and min(status) = 'closed'
             then 'closed'
             else 'open'
from t
group by id;