I recently had to clean up a database table of user data because it had the same email addresses assigned to multiple users. The first step was putting together a query that pulled only the duplicate email addresses and also told me how many times they occurred – from there the actual clean up process was fairly straight forward.
This simple query looked something like this:
SELECT email_address, COUNT(email_address) AS occurrences FROM users GROUP BY email_address HAVING occurrences > 1
I find this to be a particularly helpful SQL snippet as this kind of clean up process is fairly common. It uses MySQL’s
HAVING clause because
WHERE cannot handle aggregate functions –
COUNT() in this case.