Calculating Duplicates On a field that is Sometimes NULL

Transact-SQL
21

based on your last query,

the DUP CTE is the query that identify the duplicates. So if you have 3 fields, it will be like FirstName, LastName, Num

; WITH DUP
AS (
	SELECT  FirstName,
		LastName,
		Num,
		COUNT(*) AS NumberDuplicates
	FROM table1
	WHERE Num IS NOT NULL
	GROUP BY FirstName,
		LastName,
		Num
	HAVING (COUNT(*) > 1)
	)

the second part of the query DUP joining to table1t is for you to show the required columns.

FROM  DUP d
INNER JOIN table1t t
	ON  d.FirstName = t.FirstName
        AND d.LastName = t.LastName
        AND d.Num = t.Num       -- added this line

Yes. you are correct. So by right, you should have another JOIN condition on Num

1 Like
22

Thanks Khtan