Solved

sql for duplicate rows in table

Posted on 2013-06-04
7
302 Views
Last Modified: 2013-06-05
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
Comment
Question by:royjayd
7 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 325 total points
ID: 39220305
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39220307
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
 

Author Comment

by:royjayd
ID: 39220466
<<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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 41

Expert Comment

by:Sharath
ID: 39220552
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39220573
<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
 

Author Closing Comment

by:royjayd
ID: 39223358
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39223431
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question