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:
id | name | |
1 | mr darcy | [email protected] |
2 | luna | [email protected] |
3 | nala | [email protected] |
4 | tigger | [email protected] |
id | name | |
1 | mr darcy | [email protected] |
id | name | |
2 | luna | [email protected] |
id | name | |
3 | nala | [email protected] |
4 | tigger | [email protected] |
count(*) | names | |
[email protected] | 1 | mr darcy |
[email protected] | 1 | luna |
[email protected] | 2 | nala,tigger |
count(*) | names | |
[email protected] | 2 | nala,tigger |