List of duplicate values SQL

Question

Duplicate values can be found using this

SELECT email, COUNT(email) 
FROM users
GROUP BY email
HAVING COUNT(email) > 1

for the data

ID   NAME   EMAIL
1    John   asd@example.com
2    Sam    asd@example.com
3    Tom    asd@example.com
4    Bob    bob@example.com
5    mob    bob@example.com

But the table I need is

NAME             EMAIL
John, Sam, Tom   asd@example.com
Bob, mob         bob@example.com

Answer 1

You need the GROUP_CONCAT() function, which IIRC works in both MySql and Sqlite, but handles NULL values different between the two (I may be wrong on that last part).

SELECT group_concat(name) as Name, email
FROM users
GROUP BY email
HAVING COUNT(email) > 1