Query To Get Count of Duplicate Records

 Query To Get Count of Duplicate Records

Here I will explain How to find duplicate records in SQL server.

---Create Table

create table duplicatetest
(
id int identity(1,1),
name varchar(max)
)

                            ---Insert Some Record

                        insert into duplicatetest(name) values('krishna patil')

                        insert into duplicatetest(name) values('Mitesh patil')

                        insert into duplicatetest(name) values('Rahul Joshi')

                        insert into duplicatetest(name) values('krishna patil')

                        insert into duplicatetest(name) values('Sagar Patel')

                        insert into duplicatetest(name) values('Mitesh patil')

                        select * from duplicatetest

Now I want to know the duplicate records count that means how many times each duplicate record exists in datatable for that I written a query like this

SELECT Name, COUNT(*) as duplicate

FROM duplicatetest

GROUP BY Name

HAVING COUNT(*) > 1 Order By Name

After run above query we will get each record duplicate count that would be like this

Categories: SQL