SQLTeam.com | Weblogs | Forums

Calculating Duplicates On a field that is Sometimes NULL


#1

I am using the following query to get duplicates in a table:

SELECT FirstName, LastName, Num, COUNT() AS NumberDuplicates
FROM Table1
GROUP BY FirstName, LastName, Num
HAVING (COUNT(
) > 1)
ORDER BY NumberDuplicates DESC

I want to add to this query where it will only return duplicates where a field calle RefNum contains data.
Anyone know how I could achieve this?


#2
SELECT     FirstName, LastName, Num, COUNT(*) AS NumberDuplicates
FROM       Table1
WHERE      RefNum  IS NOT NULL
GROUP BY   FirstName, LastName, Num
HAVING     (COUNT(*) > 1)
ORDER BY   NumberDuplicates DESC

#3

Thanks for the reply khtan
But I do not want only records where RefNum IS NOT NULL but where refnum Is Null and where it Is Not Null


#4

I don't quite get that statement. can you elaborate ?


#5

When I try to add RefNum as a column to be returned like this and dont have it in the Group By I get an error message saying it RefNum must be contained in the Group By


#6

whatever columns in the SELECT that is not in aggregate must appear i the GROUP BY

SELECT FirstName, LastName, Num, COUNT(*) AS NumberDuplicates
FROM Table1
GROUP BY FirstName, LastName, Num
HAVING (COUNT(*) > 1)
ORDER BY NumberDuplicates DESC

#7

This is my actual code:

I want to return the duplicate records that are duplicates with these records but those records may not have a value in HGRefNum


#8

i think it is probably easier to understand your requirement, if you can post some sample data and expected result


#9

What I want is to return the duplicates in the table where one or other of the duplicate records contains a value in HGRefNum or not. Not all the duplicates will have a value in HGRefNum only one of them probably.

I just want to display the duplicate records and be able to see the HGRefNum field regardless of whether it holds data or not.


#10
SELECT FirstName, LastName, COUNT(*) AS NumberDuplicates
FROM Table1
GROUP BY FirstName, LastName
HAVING (COUNT(*) > 1) AND (MAX(RefNum) IS NOT NULL)
ORDER BY NumberDuplicates DESC

#11
; WITH DUP AS
(
    SELECT   FirstName, LastName, COUNT(*) AS NumberDuplicates
    FROM     Table1
    GROUP BY FirstName, LastName
    HAVING   (COUNT(*) > 1)
)
SELECT  d.*, t.RefNum
FROM    DUP d
        INNER JOIN Table1 t    ON    d.FirstName    = t.FirstName
                               AND   d.LastName     = t.LastName
ORDER BY NumberDuplicates DESC

#12

Hi KHtan,

I am trying to add the above to a Stored Procedure but keep getting error messages.
Do you know how I can add the above to a Stored Procedure?


#13

What is the error?
This should go into a stored procedure without a problem. Can you post the code?


#14

Here is the code:

INSERT INTO HDuplicates

; 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))
SELECT d .*, t .Num
FROM DUP d INNER JOIN
table1t t ON d .FirstName = t .FirstName AND d .LastName = t .LastName
WHERE (Num LIKE N'%HO%')
ORDER BY NumberDuplicates DESC

END


#15

Should be

; 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)
	)
INSERT INTO HDuplicates ( <THE COLUMN NAME HERE> )
SELECT <THE COLUMN NAME HERE>
FROM DUP d
INNER JOIN table1t t
	ON d.FirstName = t.FirstName
		AND d.LastName = t.LastName
WHERE (Num LIKE N'%HO%')
ORDER BY NumberDuplicates DESC

#16

I keep getting an error saying there is an error near ';'
Should we not be declaring DUP ?


#17

You are using SQL Server which version? This is a CTE where DUP is "declared" by the "WITH"
The starting ; really should be on the statement before and there should be a ; after the DESC


#18

I am using SQL Server 2008


#19

Good so the CTE should work.
If you move the INSERT INTO as @khtan suggested do you still get the error?


#20

This is now working khtan
If I wanted to get the duplicates based on three fields and pnum is the additional field do I just add it at the inner join as d.pnum = t.pnum ?