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

FROM tbl_user_trans
HAVING ( COUNT(userId) = 1 )

The query will return:


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') ??


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

How to apply for a new ration card and what are the documents required?

List of documents required for new ration card application in Kerala and how to apply

Application for a new ration card should be addressed to Taluk Supply Officer (TSO) / City Rationing Officer (CRO) of applicant's residing area.

Primary document required are Residence certificate, Income certificate and incase the applicant's name is in another ration card then he/she should submit the reduction certificate ontained from previous TSO / CRO as proof for removing his/her name from old Ration card.

You can get the new application from your currently residing Taluk Supply Office.

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

Joomla and Forum Integration - Integrating Forums to Joomla

Joomla is one of the most popular CMS opensource packages. It is very easier to develop website's using Joomla. You just need to download Joomla package from Joomla's Official website  and install it on your domain and later adding customizations to templates and feature and Your website is ready :). Now a days most websites provides a forum section for it users for discussing various article topics, gathering opinions etc.

Following are some best know forum opensource packages which can be integrated with Joomla and create a new forum experience for users

Urgent Openings for PHP trainees, Andriod / IOS developers and PHP developers in Kochi Trivandrum Calicut and Bangalore. Please Send Your updated resumes to   Read more »
Search This Blog