Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql for duplicate rows in table

Posted on 2013-06-04
7
Medium Priority
?
311 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1300 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 66

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 66

Expert Comment

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

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Detach & Attach 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.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

636 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