i using sql 2005 , having simple query below trap duplicates: select x,y,count(distinct z) z_count tbla group x,y having (count(distinct z) > 1) now issue column z having null values, ignored count distinct. such, duplicates having z null in 1 record , not-null in another, not getting trapped. can plz suggest how can around 1 single query? quick , dirty solution: replace null dummy-null value: select x,y,count(distinct coalesce(z, 'dummy-null')) z_count tbla group x,y having (count(distinct coalesce(z, 'dummy-null')) > 1) only possible, if can fake dummy-null value. edit: guess use count(distinct z) because rows having x=y=z shoud not considered duplicates (for reason). otherwise, duplicates of x=y use: select x,y,count(*) dup_count tbla group x,y having (count(*) > 1)