SQL Custom column based on the values in the column

Question

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

You should tag your question with the database you are using. — Gordon Linoff
– Gordon Linoff, Commented Aug 17, 2017 at 21:53

Gordon Linoff · Accepted Answer · 2017-08-17 21:53:10Z

1

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;

answered Aug 17, 2017 at 21:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

user3273269 Over a year ago

Love you sir, thanks for your quick help. darn i was in an impression that min, max functions can not be used with string :(

Collectives™ on Stack Overflow

SQL Custom column based on the values in the column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related