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
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.