How to write mysql5 query for finding duplicate rows from a table?

Question

Hi I have a table like this

ID   UserName
1     [email protected]
2     [email protected]
3     [email protected]
4     [email protected]
5     [email protected]
6     [email protected]

I need an output like this. I need only repeated rows list. How can I create this kind of an output using mysql query.

ID   UserName           Count
1     [email protected]       3
2     [email protected]      2

Please help me. Thanks.

Why [email protected] has an ID of 2 in the result?

ypercubeᵀᴹ
– ypercubeᵀᴹ

2011-07-20 10:54:03 +00:00
Commented Jul 20, 2011 at 10:54 — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jul 20, 2011 at 10:54

acme · Accepted Answer · 2011-07-20 11:38:46Z

3

I had the same problem some time ago and solved it like this (as far as I remember):

SELECT *
FROM tableA INNER JOIN
(SELECT DISTINCT MAX(id) as id, type_id, temp FROM tableA GROUP BY type_id, temp) AS t
ON tableA.id = t.id
AND tableA.type_id = t.type_id
AND tableA.temp = t.temp

You join the table with itself selecting the ids that are duplicate. The fields that should be tested against duplicate values are in this case type_id and temp. If you need more or less fields that should be considered as duplicates you can adjust the fields.

I don't know if this helps in your case and if it can be done in a more simple way, so I'm prepared for downvotes ;-)

Edit: removed last condition AND tableA.id < t.id as suggested by ypercube because it leads to 0 results.

edited Jul 20, 2011 at 11:38

answered Jul 20, 2011 at 11:03

acme

14.9k8 gold badges77 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ypercubeᵀᴹ Over a year ago

Your query will show exactly 0 rows. You have both tableA.id = t.id AND tableA.id < t.id in the joining conditions.

ypercubeᵀᴹ Over a year ago

If you remove the last condition, it will work. But there is no reason for such complexity. A simple GROUP BY can solve most such problems.

acme Over a year ago

Ok, thanks. Yeah, as I said I doubt is was the most simplest way, but at least I wanted to share this solution.

ypercubeᵀᴹ Over a year ago

Welcome. You can also remove the DISTINCT from your subquery. It's redundant there because of the GROUP BY.

ypercubeᵀᴹ Over a year ago

And what you propose can be helpful in situations when one wants to show more rows (that the grouped ones) from the table.

GargantuChet · Accepted Answer · 2011-07-20 10:44:44Z

2

It looks like you're trying to pull the following data:

First ID for a given UserName
The UserName itself
The total number of IDs for that UserName

This query should do the trick:

SELECT
  MIN(id),
  UserName,
  COUNT(id)
FROM users
GROUP BY UserName;

answered Jul 20, 2011 at 10:44

GargantuChet

5,8091 gold badge34 silver badges41 bronze badges

Comments

Talha Ahmed Khan · Accepted Answer · 2011-07-20 11:00:34Z

1

since the ID is not unique so its a bit not logical to get the sum of unique UserName from the table.

If the ID is not required we can get the result from single query.

SELECT UserName, COUNT(UserName) AS Count
FROM TableName GROUP BY UserName
HAVING COUNT(UserName) > 1;

But in the case of ID in the result it will be a more complicated query including sub-query and inner table.

edited Jul 20, 2011 at 11:00

answered Jul 20, 2011 at 10:43

Talha Ahmed Khan

15.6k10 gold badges46 silver badges49 bronze badges

2 Comments

learner Over a year ago

Hi you r using SUM it will not work. I tried this its working SELECT UserName, COUNT(UserName) AS Count FROM TableName GROUP BY UserName HAVING COUNT(UserName) > 1;

ypercubeᵀᴹ Over a year ago

It's not logical to SUM usernames. You can COUNT them though.

ypercubeᵀᴹ · Accepted Answer · 2011-07-20 10:55:38Z

0

SELECT UserName
     , COUNT(*) AS `Count` 
FROM tableX
GROUP BY UserName
HAVING COUNT(*) > 1

answered Jul 20, 2011 at 10:55

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Comments

learner · Accepted Answer · 2011-07-20 11:00:12Z

0

Hi this is the right answer.

SELECT UserName, COUNT(UserName) AS Count
FROM TableName GROUP BY UserName
HAVING COUNT(UserName) > 1;

answered Jul 20, 2011 at 11:00

learner

2,1396 gold badges23 silver badges32 bronze badges

2 Comments

ypercubeᵀᴹ Over a year ago

Then accept Talha's answer that has this. You don't need to post it as an answer yourself.

ypercubeᵀᴹ Over a year ago

Also note that COUNT(*) is faster in MySQL than COUNT(a_field). And in this case will have same results.

Collectives™ on Stack Overflow

How to write mysql5 query for finding duplicate rows from a table?

5 Answers 5

5 Comments

Comments

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related