Calculating Duplicates On a field that is Sometimes NULL

Transact-SQL
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
1 Like
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
1 Like
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
1 Like
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
1 Like
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 ?