SQL error while using alias in a GROUP BY clause , it just does not work out with full case statement

Question

I am trying to select two things customer_id and segment(based on some logic) which require both of them to be in GROUP BY

I tried using without alias but it failed as there is MAX in a a GROUP BY filedname . What should I do to take care off this ?

Answer 1

Try the below query. Hope it will do the work

SELECT distinct 
t1.CUSTOMER_ID AS CUSTOMER_ID,
CASE WHEN tab.SEGMENT = 1 AND t2.CUSTOMER_ID IS NOT NULL THEN 'SweepAcquired'
ELSE 'NonSweepAcquired' END AS NEW_SEGMENT
FROM tablename1 t1, tablename2 t2
join
(
select CUSTOMER_ID,
max(CASE WHEN t2.event_date=t1.event_date 
AND t2.SOURCE_TYPE='sweepstakes'  
AND t2.SOURCE_ID IS NOT NULL 
AND lower(t2.SOURCE_ID) <> 'unknown' THEN 1 ELSE 0 END) AS SEGMENT
from tablename1 t1
join tablename2 t2
on t1.CUSTOMER_ID=t2.CUSTOMER_ID
) tab on t1.CUSTOMER_ID=tab.CUSTOMER_ID
WHERE t1.CUSTOMER_ID=t2.CUSTOMER_ID(+)
AND t1.MARKETPLACE_ID = '{MARKETPLACE_ID}'
AND lower(t2.event_type(+))='like'
AND lower(t2.SOURCE_TYPE(+))='sweepstakes'
AND t2.SOURCE_ID(+) IS NOT NULL
AND lower(t2.SOURCE_ID(+)) <> 'unknown'
AND t2.ENTITY_ID(+)='{FREE_FORM}'
GROUP BY t1.CUSTOMER_ID, NEW_SEGMENT;