Akanksha Akanksha - 5 months ago 7
MySQL Question

Get the duplicate rows with the number of occurrence in MYSQL

I need to list all the duplicate IDs with the number of occurrence of each ID in a single MYSQL query.

ID
____
1
1
2
3
4
4
4
5
5
6
7


Output must be:

ID | Occurrence
_______________
1 | 2
4 | 3
5 | 2

Answer

Just use a simple GROUP BY query:

SELECT ID, COUNT(*) AS Occcurrence
FROM yourTable
GROUP BY ID
HAVING COUNT(*) > 1