duplicates - MySQL: select phones that belongs to different users -

i have task can't solve myself because of lack of experience, , need help.

let's assume have table following fields: "phone_number" , "user_id". goal find numbers belongs more 1 user.

the result should looks that:

+---------------------------+---------------------------------+
|     phone_number     |     counts     |     users     |
+---------------------------+---------------------------------+
|     (xxx)xxx-xxx         |         5         |     1, 5, 9    |
+---------------------------+---------------------------------+

phone_number - phone number repeats
counts - how many times number repeats
users - ids of users having number, separated comma. example (1, 5, 9)

i have following query finds number duplicates, need compare id of user. , ids of users number duplicated.

select `number`, count(`number`) `count`, `user_id` `phones`  group `number` having `count` > 1 

thank :)

you can use group_concat on user_id column said desired result

example:

select  `number` `phone_number`,          count( `number` ) `counts`,         group_concat( `user_id` ) `users`    `phones`   group  `number`  having  `counts` > 1