mercredi 9 juin 2021

Use a count function in a case statment

I have the below code for which I need to enter a count function in one condition of the case statement and then group by the case statement like below. But it is not letting me group the case statement. I essentially want to create a third bucket called "Both" with the condition below

select  
CASE
  WHEN i.CLASSIFICATION in ('CUSTOMER','PARTNER') THEN 'API'
  WHEN i.CLASSIFICATION in ('MOBILE','NDSE') THEN 'APP' 
  WHEN (i.CLASSIFICATION in ('MOBILE','NDSE','CUSTOMER','PARTNER') OR count(distinct UPPER(p.ACCOUNTID)) >=2) THEN 'BOTH' 
  ELSE 'Other' END AS "Bucket", 
count(distinct ACCOUNTID) as No_Accounts,count(distinct envelopeid_hash) as numenvelopes
from ENVELOPE_TABLE e   
JOIN DIMDATE_TABLE b on to_date(e.sentinitial)=b.standarddate
join PAYING_TABLE p
on UPPER(e.ACCOUNTID)=UPPER(p.ACCOUNTID) 
LEFT JOIN IK_TABLE i ON i.IntegratorKeyId = e.InitiatingIntegratorKeyId AND i.SourceKey = e.SourceKey
group by 1

Aucun commentaire:

Enregistrer un commentaire