Skip to main content

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

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 :)




Popular posts from this blog

Payback Points - How to redeem - How to merge multiple payback accounts - Block Payback card - Payback customer care

Your SBI Debit card ending with XX0000 is deactivated only for Internet txn.

SBI account holders may have received an SMS with following message, supposed to be from State Bank of India (SBI).

Your SBI Debit card ending with XX0000  is deactivated only for Internet txn. To activate send SMS "SWON ECOM 0000" to 09223966666. No change for ATM/POS usage
** Replace the four Zeros with last 4 digits of your debit card number

Recently many of the SBI account holder has losed their money due to a hi-tech ATM robbery which happened in Thiruvananthapuram, capital city of Kerala.

How to turn off BSNL Buzz services - Steps to deactivate BSNL Buzz messages

BSNL Buzz is a service from BSNL for mobile customers, in which when your phone is in idle mode, teasers or small buzz messages related to topics like entertainment, lifestyle, sports, business, fun, polls, daily news updates are delivered to your mobile. When you activate a BSNL SIM card, through which BSNL BUZZ is available, you automatically accept the terms and conditions for teaser delivery.

Recently, I bought a new BSNL SIM card. After the activation, when ever I unlock the phone, I was getting lots of buzz message one after another. I have to click on cancel / back button multiple time to view the home screen. Some times when some call arrives, it was difficult to attend calls due to this teaser popups. So I decided to deactivate the BSNL buzz. Here are the steps to Deactivate or Turn Off BSNL Buzz.


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 »
Member
Search This Blog