max(case when …) syntax issue

January 28, 2022

select 
complaint_id
,complaint_type
,COMMUNICATION_ID
,max(case when delivery_type='deliver_once' then '1' else '0' end as IS_ADDON_REFUND) 
from 
complaints_order_status 
group by 1,2,3

what’s the issue with it?

>Solution :

You can’t name your column inside the MAX function. Also use integers for 1 and 0. Also, if you could paste in the error message you’re getting it would be helpful. But try:

select complaint_id ,complaint_type ,COMMUNICATION_ID ,max(case when delivery_type='deliver_once' then 1 else 0 end) as IS_ADDON_REFUND from complaints_order_status group by complaint_id ,complaint_type ,COMMUNICATION_ID