How To Remove Duplicates from a Count Query

[txdave35]

Hey guys,


I am with duplicate records in a join table. I am trying to retrieve the number of active employees who have a benef code of K in another table called HIST.

This query gives me the correct number:

select count(A.mbr_ssn_nbr) from dsnp.pr01_t_mbr_sys

However, when I join the hist table, my count goes way up. I discovered that the problem is the same SSN number will have multiple records in the HIST table.


So this query returns too many records

select count(A.mbr_ssn_nbr) from dsnp.pr01_t_mbr_sys A,
dsnp.pr01_t_mbr_hist B

where A.mbr_ssn_nbr = B.mbr_ssn_nbr

and B.benef_stat_cd = 'K'


I tried this query:

select count(A.mbr_ssn_nbr) from dsnp.pr01_t_mbr_sys A,
dsnp.pr01_t_mbr_hist B

where A.mbr_ssn_nbr = B.mbr_ssn_nbr

and mbr_stat_cd = '1'
and exists (select mbr_ssn_nbr from dsnp.pr01_t_mbr_hist
group by mbr_ssn_nbr
having count(mbr_ssn_nbr) = 1)



But it still gives me a really high number. Can someone tell me if my logic is wrong, or a different way to approach the problem? Thanks so much!

 

[JohnDTampaBay]

select count(DISTINCT A.mbr_ssn_nbr)
from dsnp.pr01_t_mbr_sys A,
dsnp.pr01_t_mbr_hist B
where A.mbr_ssn_nbr = B.mbr_ssn_nbr
and B.benef_stat_cd = 'K'



--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)