Home  »     »  How to find duplicate records in a table. - MySQL or SQL Query to find duplicates in a table

How to find duplicate records in a table. - MySQL or SQL Query to find duplicates in a table

Friday, February 3, 2012

How to find duplicate records in a table?

MySQL or SQL Query to find duplicates in a table

suppose you have a table like this which represent some user transactions:

Table Name : tbl_user_trans

transId userId transDate transAmount
1000 USR034 2012-01-01 300.25
1001 USR004 2012-01-08 100.05
1002 USR030 2012-01-11 30.59
1003 USR034 2012-01-08 1000.65
1004 USR050 2012-02-01 19.25
1005 USR034 2012-02-03 403.25



If want to find all Users from the transaction table who had made transactions more than once , then you have to do like this:


SELECT userId, COUNT(userId) AS HowMany FROM tbl_user_trans
GROUP BY userId
HAVING ( COUNT(userId) > 1 )


The query will return
userId      HowMany
USR034     3

You could also find users who had made single transaction, Do it like this:


SELECT userId
FROM tbl_user_trans
GROUP BY userId
HAVING ( COUNT(userId) = 1 )


The query will return:

userId      
USR004
USR030
USR050
  


From User comments:
what if I need to check for a particular record not a column?
i.e (How many duplicated record are there for 'USR034') ??

TRY THIS

SELECT userId, count( * )
FROM tbl_user_trans
GROUP BY transId,userId,transDate,transAmount
HAVING count( * ) >1

this query will pull results by checking exact column match for each records


Hope this helped some one :)







How to link to this page?
If you wish to link to this page from your website, simply Copy and paste the above HTML code to your web page. It will appear on your page as:
How to find duplicate records in a table. - MySQL or SQL Query to find duplicates in a table.




Share this!




comments powered by Disqus

This Weeks 7 Popular Posts


Subscribe to Recent Posts by Email
Stay connected to CROZOOM with regular Email notices of new Techie articles and IT Jobs. Updates will be delivered to your Inbox as soon as they are posted online.

Enter Your Email Address:  

Delivered by FeedBurner   RSS Feed

Search this Blog   



Urgent Openings for PHP trainees, Andriod / IOS developers and PHP developers in Kochi Trivandrum Calicut and Bangalore. Please Send Your updated resumes to recruit.vo@gmail.com   Read more »