'max(case when ...) syntax issue

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?

mysqlsql


Solution 1:[1]

I have changed two things from your code:

  • Use the alias in Group by. 1,2,3 don't work on all platforms.
  • Give alias outside max clause bracket. There is no need to give a name to case when clause when this is not your final output.

Here is the final code:

  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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Shu Rahman

Related Questions