Search code examples
mysqlsqlcounthaving-clause

COUNT WHERE two conditions are met in the same column

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

Solution

  • 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;