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

logic:


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 :

ID SUBID Status
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'
         end)
from t
group by id;