Find duplicate rows in MySQL

SELECT email, COUNT(*) AS n
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY n DESC;

When you need this

Duplicate customers are one of the most common problems in business data. They come from the same person signing up twice, a CSV imported two times, or staff typing an entry again because they couldn't find the first one. They mess up your counts and they mean the same person gets two of every email.

How it works

GROUP BY email puts all rows with the same email together. COUNT(*) counts how many are in each group. HAVING COUNT(*) > 1 keeps only the groups with more than one row, which are your duplicates. ORDER BY n DESC puts the worst ones first.

Catching the near-duplicates

Asha@Gmail.com and asha@gmail.com (with a trailing space) are the same person, but the query above treats them as different. Clean while grouping:

SELECT LOWER(TRIM(email)) AS email_clean, COUNT(*) AS n
FROM customers
GROUP BY email_clean
HAVING n > 1;

Seeing the actual rows

To see every duplicate row, not just the count:

SELECT c.*
FROM customers c
JOIN (SELECT email FROM customers GROUP BY email HAVING COUNT(*) > 1) d
  ON d.email = c.email
ORDER BY c.email, c.id;

Before deleting anything

Don't delete straight away. First check which copy has the orders, payments or notes linked to it, because that's the one to keep. Take a backup (mysqldump) before any bulk DELETE. Once the data is clean, add a UNIQUE index on email so it can't happen again.