In my previous article SQL SERVER – Delete Duplicate Records – Rows, we have seen how we can delete all the duplicate records in one simple query. In this article we will see how to find count of all the duplicate records in the table. Following query demonstrates usage of GROUP BY, HAVING, ORDER BY in one query and returns the results with duplicate column and its count in descending order.
SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUPÂ BY YourColumn
HAVING COUNT(*)Â > 1
ORDERÂ BY COUNT(*) DESC
Watch the view to see the above concept in action:
[youtube=http://www.youtube.com/watch?v=ioDJ0xVOHDY]Reference : Pinal Dave (https://blog.sqlauthority.com) , SQL SERVER – Delete Duplicate Records – Rows
187 Comments. Leave new
Another solution is to put the table with duplicates in join with itself.
For a simple example see:
thx pinal…. it was very easy …
Pinal,
The query posted at the top of this page results with duplicate column and its count in descending order.
How can i find the count the returned rows???
Please help me.
Try this
SELECT COUNT(*) as total_duplicates FROM
(
SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
) as t
that’s the solution for my problem – thanks a million
Hi Pinal,
I have a question.
I need to loop through a table and if there are some duplicate values in one of the columns I need to add a suffix of a, b, c, and so on. But, there are different sets of duplicates. So, I need to do the suffix on each separate set of duplicates.
Please notice bellow, there are three sets of values that are duplicates. This is a sample of my data.
Could you give me any ideas on how to accomplish this task? Thank you.
ZP19188AMR
ZP19188AVE
ZP19188AVH
ZP19188AVH
ZP19188COC
ZP19188COC
ZP19188COC
ZP19188COC
ZP19188COC
ZP19188COC
ZP19188PHM
ZP19188PHM
ZP19188RIM
ZP19188SAB
ZP19188WEH
select col, count(col) from table
group by col
having count(col)>1
thanx.. its short and effective… worked for me..
thanks for the post it really helped me.
Thanks. This is very helpful.
I need to limit the number of duplicates. Can this be achieved? Thanks.
Use that limit in place of N in the following
Having count(*)>N
Thanks.
HI Pinal,
If I want to select all the fields in a table,
How shold I write the query for that
thank u
select t1.* from table as t1 inner join
(
select col from table group by col having count(col)>1
) as t2
on t1.col=t2.col
thanks.
select Taluk_Name_Eng ,count(Taluk_Name_Eng)as kk from tbl_PL04_Taluk
what problem in this query
You need to include that column in the group by clause
select Taluk_Name_Eng ,count(Taluk_Name_Eng)as kk from tbl_PL04_Taluk group by Taluk_Name_Eng
Using the above Query is nearly just what I need but it only gives me an ID (YourColumn). I have another table which would have a named value for this ID. How can I return that instead? A Join or a nested statement?
Many thanks
Did it, quite new to SQL I’m afriad, hope it helps another novice :)
SELECT top 20 computers.name as CID, COUNT(*) as ErrorCount
FROM errors
inner join computers
on computers.id = errors.computerid
GROUP BY computers.name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
Hi,
I have written to get Duplicate Rows from the table. You can use the ROW_Number() in SQL 2005.
To read more about it:
Tejas
u r good
SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
this query is solved my lot of problems
Thank you very much
If you’d like to get a total count of the duplicates as I needed to do, try returning the above query as a derived table and then do a sum on the count.
SELECT SUM(Duplicates.TotalCount) FROM (SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1) AS Duplicates
My database has over 1200 duplicates. :(
Hey Pinal,
Excellent query ! thanks !
its really good update its help ful for every one thanks
i have a data entry system for patient records. in my system there are several forms through which data is entered in different tables. and there is a form which takes the present condition of the patient (i call it PresentAssessment). now i what i want is when the user enters a new record i want to compare the values of the attributes (database table fields) of the newly entered patient and from all the matching records i want to calculate and return the chance(in terms of %age) of particular disease(s) (stored in PresentAssessment) to newly added patient. e.g. if i have two tables i.e. PatientDrugHistory and PresentAssessment. and say each of them already have 10 records with 10 columns each. now when entering the 11th record in PatientDrugHistory i want to calculte and store how much chances (in terms of %age) exist that this 11th patient is suffereing from disease1 (a column in PresentAssessment). please if anybody can tell me what query to write for this purpose