Scenario:
You are working as SQL Server developer. You need to write the query for dbo.Customer table that should return the duplicate records and count of duplicate record.
Solution:
You can use Group by and Count aggregate function to find all the duplicate records in table and count.
Let's say that we have dbo.Customer table with below definition and data. We would like to find the record if it is duplicate by columns FName,LName and CountryShortName.
Create table dbo.Customer
(Id int,
FName VARCHAR(50),
LName VARCHAR(50),
CountryShortName CHAR(2),
SaleAmount Int)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87),
(6,'Raza','M','Pk',Null)
Let's write our query, we have include all the columns in select list with count(*) and then group by the same columns. At the end we will be using Having clause to filter the record which are duplicate.
SELECT fname,
lname,
countryshortname,
Count(*) AS RecordCount
FROM dbo.customer
GROUP BY fname,
lname,
countryshortname
HAVING Count(*) > 1
As from data we can see that the only record which is duplicate by FName,LName and Country short name is
'Raza','M','PK'and have total two records as returned by our query.
How to find duplicate records by using Group by and Having Clause in SQL Server
