Count Distinct with NULL retention

Question

Hello,

I am using SQL 2005 and having a simple query as below trap duplicates:

SELECT x,y,COUNT(DISTINCT z) AS z_count 
FROM tblA 
GROUP BY x,y 
HAVING (COUNT(DISTINCT z) > 1)

Now the issue is this column z is sometimes having NULL values, which get ignored by count distinct. As such, duplicates having z as NULL in one record and as not-NULL in another, are not getting trapped.

Can anyone plz suggest how can I get around with this with one single query?

Cheers!

Answer 1

Quick and dirty solution: Replace null by dummy-null value:

SELECT x,y,COUNT(DISTINCT Coalesce(z, 'dummy-null')) AS z_count 
FROM tblA 
GROUP BY x,y 
HAVING (COUNT(DISTINCT Coalesce(z, 'dummy-null')) > 1)

Only possible, if you can fake a dummy-null value.

Edit: I guess you use Count(distinct z) because rows having x=y=z shoud not be considered duplicates (for some reason). Otherwise, to get ALL duplicates of x=y use:

SELECT x,y,COUNT(*) AS dup_count
FROM tblA 
GROUP BY x,y
HAVING (COUNT(*) > 1)