• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

sql for duplicate rows in table

hi experts

I have table Customer with 4 columns and 100 rows of data.
Is there a handy sql to check if there are duplicate rows of data?

thanks
0
royjayd
Asked:
royjayd
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl < air code > ...

SELECT Column1, Column2, Column3, Column4, Count(Column4) as duplicate_rows
FROM YourTable
GROUP BY Column1, Column2, Column3, Column4
HAVING COUNT(Column4) > 1
ORDER BY COUNT(Column4) DESC
0
 
Surendra NathCommented:
yes, do the below
;with cte
(
 select row_number() over(parition by <all your columns> order by (select 1)) rn,* from <your table>
)
select * from cte where rn>1

Open in new window


in the above code, replace the
<all your columns> with the columns list of your table
<your table> with your table name.
0
 
royjaydAuthor Commented:
<<SELECT Column1, Column2, Column3, Column4, Count(Column4) as duplicate_rows
FROM YourTable
GROUP BY Column1, Column2, Column3, Column4
HAVING COUNT(Column4) > 1
ORDER BY COUNT(Column4) DESC >>

looks good, i am trying to get a total sum of the duplicate rows
but this doesnt seem to work

SELECT Column1, Column2, Column3, Column4, Count(Column4) as duplicate_rows,
SUM(duplicate_rows)
FROM YourTable
GROUP BY Column1, Column2, Column3, Column4
HAVING COUNT(Column4) > 1
ORDER BY COUNT(Column4) DESC

thanks
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
SharathData EngineerCommented:
try this.
SELECT Column1,Column2,Column3,Column4,duplicate_rows,
      SUM(duplicate_rows) OVER ( partition BY (SELECT 1)) sum_duplicate_rows
  FROM (SELECT Column1,Column2,Column3,Column4,COUNT(Column4) AS duplicate_rows 
          FROM YourTable 
         GROUP BY Column1,Column2,Column3,Column4 
        HAVING COUNT(Column4) > 1) t1 
 ORDER BY duplicate_rows DESC 

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<somewhat redundant with the above comments>

>i am trying to get a total sum of the duplicate rows
Throw the whole thing in a subquery, and sum.  
Replace the SUM with COUNT if you want distinct duplicate row columns.

SELECT SUM(a.duplicate_rows)
FROM (
   SELECT Column1, Column2, Column3, Column4, Count(Column4) as duplicate_rows,
   SUM(duplicate_rows)
   FROM YourTable
   GROUP BY Column1, Column2, Column3, Column4
   HAVING COUNT(Column4) > 1
   ORDER BY COUNT(Column4) DESC ) a
0
 
royjaydAuthor Commented:
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now