How to find duplicate records by using Group by and Having clause in SQL Server - SQL Server / TSQL Tutorial Part 132

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



1 comment:

  1. These NCERT Model Question Paper 2023 for 10th Class based on the latest Revised are a helpful Resource for 10th Class Students. They must Practice All the NCERT Sample Paper 2023 for 10th Class and Should not Leave any stone Unturned for their Public Exam Preparation Students can have a Look at the NCERT Question Paper 2023 asked in the exam for NCERT 10th Class Model Paper 2023 All Hindi, English Medium Subjects, Students should also Check the Marking Scheme to Understand how to write Proper Answers According to the Step-wise marks Distribution Scheme, by going Through the NCERT important Question Paper 2023, This will help Students in Understanding the difficulty level of Exam.

    ReplyDelete