I’m sure this has been asked before, but I can’t find a similar situation:
Given a table 'activity'
user_ID | contact_channel_ID 123456 email 123456 email 555555 SEM 555555 SEM 995959 phone 995959 SEM 696969 email 696969 email
I need to count all users that have ‘SEM’ AND at least another channel IN contact_channel_ID
Advertisement
Answer
You can use aggregation and having
:
select user_id from activity group by user_id having sum(case when contact_channel_ID = 'SEM' then 1 else 0 end) > 0 and sum(case when contact_channel_ID <> 'SEM' then 1 else 0 end) > 0;
In MySQL, the having
clause can be shorted to:
having sum(contact_channel_ID = 'SEM') > 0 and sum(contact_channel_ID <> 'SEM') > 0;