Sum distinct records in a table with duplicates

Database
Enthusiast

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!

Tags (3)
1 REPLY
Teradata Employee

Re: Sum distinct records in a table with duplicates

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