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