Search code examples
mysqlduplicatesgroup-concat

MYSQL : list all occurrences of duplicates in a table

I want to list all occurrences of duplicates in a table and regroup them by their id.

Here is my table :

+------------+----+
| name       | id |
+------------+----+
| Produit 78 |  5 |
| Produit 78 |  6 |
| Produit 78 | 11 |
| Produit 69 | 12 |
| Produit 12 | 14 |
| Produit 12 | 15 |
| Produit 92 | 22 |
| Produit 92 | 25 |

I've tried this :

select name, id from commande_ligne group by name having count(name) > 1;

But it only list me the duplicate with their first occurrence.

Whereas I would like something like this :

+------------+-------+
| name       | id    |
+------------+-------+
| Produit 78 | 5,6,11|
| Produit 12 | 14,15 |
| Produit 92 | 22,25 |
Solution

  • If you want all the values of id among the duplicates, you can do it this way:

    select name, GROUP_CONCAT(id) 
from commande_ligne 
group by name having count(name) > 1;

    Read more about the GROUP_CONCAT() function.