MySQL query for obtaining unique list of domain names with count from email field

The following MySQL query is useful for getting a count of all the domain names used by email addresses in your database.  It isolates the domain name (after the @ sign) in the email field.

SELECT substring_index(email, '@', -1), COUNT(*)
FROM login
GROUP BY substring_index(email, '@', -1);

It can also be used to track down spammers or disabled accounts:

SELECT substring_index(email, '@', -1), COUNT(*)
FROM login
WHERE status = 'disabled'
GROUP BY substring_index(email, '@', -1);

Link to MySQL String functions reference.

Link to MySQL substring_index() function.

When I find a useful little snip like this I like to share it (and document it in my blog so I can remember it)!

Post inspired by: http://stackoverflow.com/questions/2440429/mysql-query-to-count-unique-domains-from-email-address-field

This entry was posted in Code, Data. Bookmark the permalink.

Comments are closed.