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

Hi I have a table like this

ID   UserName
1     test@test.com
2     test@test.com
3     john@stack.com
4     test@test.com
5     adam@stack.com
6     john@stack.com

I need an output like this. I need only repeated rows list. How can I create this kind of an ou开发者_C百科tput using mysql query.

ID   UserName           Count
1     test@test.com       3
2     john@stack.com      2

Please help me. Thanks.

---

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.

---

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;

---

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.

---

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

---

Hi this is the right answer.

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