Find Duplicate Row Count in Table

In this blog, we’ll discuss a simple way to find duplicate record count of any column in a table.

There are numerous occasions, when we like to find if any column is having a duplicate row and if yes then we may also need to find the number of total counts the duplicate value is occurring in the table.

;WITH DUPLICATEROWS AS
(
SELECT
/* Name of the Column hard coded here just for reference that, we are searching duplicate value for this column */
‘[Col1]’ DuplicateColName
/* It’ll help in getting the count of duplicates value found in table */
,[Col1] DuplicateColValue
/* It is the main logic to partition and order by the table with the column we are searching for duplicate value */
,Row_Number() OVER (PARTITION BY [Col1] ORDER BY [Col1]) DuplicateRowNo
FROM
SchemaName.TableNameĀ — Table name with schema
)
SELECT DuplicateColName,DuplicateColValue,Count(DuplicateRowNo) DuplicateCount
FROM DUPLICATEROWS WHERE DuplicateRowNo >1
Group By DuplicateColName,DuplicateColValue
Order By DuplicateColValue

Example1
/* Create a table with duplicate values*/
create table test_t1(ID int null)
go
insert into test_t1 values(null),(null),(1),(2),(2),(4),(1),(null),(null),(1),(2),(2),(4),(1)
go

;WITH DUPLICATEROWS AS
(
SELECT
‘[ID]’ DuplicateColName
,[ID] DuplicateColValue
,Row_Number() OVER (PARTITION BY [ID] ORDER BY [ID]) DuplicateRowNo
FROM
[DBO].[test_t1]
)
SELECT DuplicateColName,DuplicateColValue,Count(DuplicateRowNo) DuplicateCount
FROM DUPLICATEROWS WHERE DuplicateRowNo >1
Group By DuplicateColName,DuplicateColValue
Order By DuplicateColValue

/* Query Output */

DuplicateColName DuplicateColValue DuplicateCount
[ID] NULL 3
[ID] 1 3
[ID] 2 3
[ID] 4 1

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s