All Forums Database
txwylde 5 posts Joined 06/10
27 Aug 2016
Sum distinct records in a table with duplicates

I have a table that has some duplicates. I can count the distinct records to get the Total Volume. When I try to Sum when the CompTia Code is B92 and run distinct is still counts the dupes.
 
Here is the query:

select
a.repair_week_period,
count(distinct a.notif_id) as Total_Volume,
sum(distinct case when a.header_comptia_cd = 'B92' then 1 else 0 end) as B92_Sum
FROM artemis_biz_app.aca_service_event a
where a.Sales_Org_Cd = '8210'
and a.notif_creation_dt >= current_date - 180
group by 1
order by 1
;

Is There a way to only SUM the distinct records for B92?
I also tried inner joining the table on itself by selecting the distinct notification id and joining on that notification id, but still getting wrong sum counts.
 
Thanks!

Fred 1096 posts Joined 08/04
28 Aug 2016

How about
count(distinct case when a.header_comptia_cd = 'B92' then a.notif_id else NULL end) - 1 /* Don't include NULL in the count */ as B92_Sum
 

You must sign in to leave a comment.