Find duplicate emails with HAVING

This query finds duplicate email addresses in a clients table:

 SELECT email, count(*), group_concat(name, ',') AS names
 FROM clients
 GROUP BY email
 HAVING count(*) > 1

Let's go through that query one step at a time:

1 FROM clients

id name email
1 mr darcy [email protected]
2 luna [email protected]
3 nala [email protected]
4 tigger [email protected]

2 GROUP BY email

id name email
1 mr darcy [email protected]
id name email
2 luna [email protected]
id name email
3 nala [email protected]
4 tigger [email protected]

3 SELECT email, count(*), group_concat(name, ',') AS names

email count(*) names
[email protected] 1 mr darcy
[email protected] 1 luna
[email protected] 2 nala,tigger

4 HAVING count(*) > 1

email count(*) names
[email protected] 2 nala,tigger