Count duplicate records or rows in SQL Server

To count all the duplicate records in a column of the table use this code:

SELECT Column_name, COUNT(*) Count_Duplicate
FROM Table_name
GROUP BY Column_name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

To count all the duplicate records in two columns of the table:

SELECT Column1, Column2, COUNT(*) Count_Duplicate
FROM Table_name
GROUP BY Column1, Column2
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

To count all the duplicate records in all columns of the table:

SELECT , COUNT(*) Count_Duplicate
FROM Table_name
GROUP BY HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

To see how to delete all the duplicate records in one query using CTE read article:
Delete Duplicate Rows in SQL Server 2005 and 2008 using CTE

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">