Get Ids of rows that has duplicates in a column

I have the following table users

id | phone    
--------------
1  | +1111    
2  | +2222    
3  | +3333    
4  | +4444    
5  | +1111

I need to get all ids, where users have duplicate phone. For example, the query must return the following dataset: [1,5]. Because user with id 1 has phone +1111 and user with id 5 has phone +1111.

There is something close in this answer. But I need to modify it for mysql. Currently, I have the following sql:

select  group_concat(id) as id from users
group by  phone
having count(id) > 1

But it returns a response with not wery good format. See:

Of corse, the reason is group_concat.

So, I need to get result as list of ids, not list of groups. Ho to do that for mysql?

Answer

Of course, the Group Sum should be the preferred solution on MySQL 8, but this is a perfect case for a simple Subquery:

SELECT *
FROM users
WHERE phone IN
 (
   SELECT phone
   FROM users
   GROUP BY phone
   HAVING COUNT(*) > 1
 )

If you need to display the actual count, go with Tim’s solution.

Advertisement

Answer