CODEWITHSUNDEEP
sqlt-sql
Welldonebacon
Welldonebacon

Reputation: 83

Returning sum case on distinct values

the results this returns includes if the result code has been done twice i.e. 1 record of data but PZ01 being used twice on the same record. I'm trying to only count it once. Each record has a unique ID which I don't want to show in this report. So I want this to run giving me the sum but from unique record ids so it is not counted more than once.

    select
    sum(case when result_code = 'PZ01' then 1 else 0 end) as Hub_Box_Activated
    ,sum(case when result_code = 'NOTI' then 1 else 0 end) as Not_Interested
    ,sum(case when result_code = 'PZ02' then 1 else 0 end) as No_Tablet_Installed
    ,sum(case when result_code = 'PZ03' then 1 else 0 end) as Customer_Query
    ,sum(case when result_code = 'PZ26' then 1 else 0 end) as NI_Security_Concerns
    ,sum(case when result_code = 'PZ27' then 1 else 0 end) as NI_Storage_Concerns
    ,sum(case when result_code = 'PZ28' then 1 else 0 end) as NI_Using_alternative_provider
    ,sum(case when result_code = 'PZ29' then 1 else 0 end) as NI_Too_Much_Hassle
from
    history
where
    list_id in ('1432','1604','1607')

Upvotes: 1

Views: 3652

Answers (2)

uzi
uzi

Reputation: 4146

Try count with distinct. Just change record_id in the query with the name of your column

select
    count(distinct case when result_code = 'PZ01' then record_id end) as Hub_Box_Activated
    ,count(distinct case when result_code = 'NOTI' then record_id end) as Not_Interested
    ,count(distinct case when result_code = 'PZ02' then record_id end) as No_Tablet_Installed
    ,count(distinct case when result_code = 'PZ03' then record_id end) as Customer_Query
    ,count(distinct case when result_code = 'PZ26' then record_id end) as NI_Security_Concerns
    ,count(distinct case when result_code = 'PZ27' then record_id end) as NI_Storage_Concerns
    ,count(distinct case when result_code = 'PZ28' then record_id end) as NI_Using_alternative_provider
    ,count(distinct case when result_code = 'PZ29' then record_id end) as NI_Too_Much_Hassle
from
    history
where
    list_id in ('1432','1604','1607')

Upvotes: 3

EzLo
EzLo

Reputation: 14189

Try using COUNT with DISTINCT on a forced 1-value CASE statement. Note that this will only yield values 1 or 0, since counting distinct result_code with a particular value will always return 1 if any exists or 0 if it doesn't.

  select
    COUNT(DISTINCT(case when result_code = 'PZ01' then result_code end)) as Hub_Box_Activated
    ,COUNT(DISTINCT(case when result_code = 'NOTI' then result_code end)) as Not_Interested
    ,COUNT(DISTINCT(case when result_code = 'PZ02' then result_code end)) as No_Tablet_Installed
    ,COUNT(DISTINCT(case when result_code = 'PZ03' then result_code end)) as Customer_Query
    ,COUNT(DISTINCT(case when result_code = 'PZ26' then result_code end)) as NI_Security_Concerns
    ,COUNT(DISTINCT(case when result_code = 'PZ27' then result_code end)) as NI_Storage_Concerns
    ,COUNT(DISTINCT(case when result_code = 'PZ28' then result_code end)) as NI_Using_alternative_provider
    ,COUNT(DISTINCT(case when result_code = 'PZ29' then result_code end)) as NI_Too_Much_Hassle
from
    history
where
    list_id in ('1432','1604','1607')

Upvotes: 2

Related Questions