I'd like to get a better idea of what domains my customers are using. I could easily do this in PHP by explode
ing each address and counting the domain that way. But I'm wondering if there's a way to get this information with just a plain MySQL query?
This is what sample output would look like:
gmail.com | 3942
yahoo.com | 3852
hotmail.com | 209
... and so on, where the first column is the email addresses domain, and the 2nd column is the number of addresses at that domain.
You would have to do something like this:
SELECT substring_index(email, '@', -1) domain, COUNT(*) email_count
FROM table
GROUP BY substring_index(email, '@', -1)
-- If you want to sort as well:
ORDER BY email_count DESC, domain;
Adding ORDER BY to WoLpH's answer makes the output more clear:
SELECT substring_index(email, '@', -1), COUNT(*) AS MyCount
FROM `database`.`table`
GROUP BY substring_index(email, '@', -1)
ORDER BY MyCount DESC;
Small tweak to Wolph's original above to shorten a bit and add nice column name and limit results in case list is long. Adjust limit to your own liking
select substring_index(email, '@', -1) AS domain, count(*) from TABLE group by domain order by count(*) DESC limit 40;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With