CODEWITHSUNDEEP
sqlsql-serversumcase
NT-Hero
NT-Hero

Reputation: 77

Exclude records that have sum greater than 1

I have query returning details of customers that are subscribed to channel xyz or all other channels. To generate this results i am using the following query: 

select customerID
,sum(case when channel='xyz' then 1 else 0 end) as 'xyz Count'
,sum(case when channel<>'xyz' then bundle_qty else 0 end) as 'Other'
From temptable

So my Question is, how do i Exclude customers that are subscribed to 2 channels, where one is xyz and one is another channel.

Upvotes: 0

Views: 99

Answers (2)

juergen d
juergen d

Reputation: 204884

select customerID
      ,sum(case when channel='xyz' then 1 else 0 end) as 'xyz Count'
      ,sum(case when channel<>'xyz' then bundle_qty else 0 end) as 'Other'
From temptable
group by customerID
having sum(case when channel= 'xyz' then 1 else 0 end) > 0
   and sum(case when channel<>'xyz' then 1 else 0 end) > 0

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270583

First, your query is not correct. It needs a group by. Second, you can do what you want using having:

select customerID,
       sum(case when channel = 'xyz' then 1 else 0 end) as xyz_Count,
       sum(case when channel<>'xyz' then bundle_qty else 0 end) as Other
From temptable
group by customerID
having count(*) = 2 and
       sum(case when channel = 'xyz' then 1 else 0 end) = 1;

If customers can subscribe to the same channel multiple times, and you still want only "xyz" and another channel, then:

having count(distinct channel) = 2 and
       (min(channel) = 'xyz' or max(channel) = 'xyz')

Upvotes: 0

Related Questions