Every so often I find some statistical need that although Perl program would be easy to write for it, its probably something the database should just handle. So I have a column in an email management table that has just the email addresses in the format user@domain.tld.
I want to know which domains make up the majority of the users. (The numbers have been changed to protect the innocent):
mysql> SELECT SUBSTRING_INDEX(email, '@', -1) as Domain, count(*) as Total
FROM email_list
GROUP BY Domain
ORDER BY Total DESC
LIMIT 15;
+----------------+---------+
| Domain | Total |
+----------------+---------+
| yahoo.com | 1304000 |
| hotmail.com | 908400 |
| aol.com | 800000 |
| msn.com | 168000 |
| gmail.com | 161000 |
| comcast.net | 143000 |
| sbcglobal.net | 110000 |
| bellsouth.net | 62000 |
| cox.net | 58000 |
| verizon.net | 56000 |
| earthlink.net | 52000 |
| charter.net | 46000 |
| juno.com | 30000 |
| optonline.net | 22000 |
| netzero.com | 17000 |
+----------------+---------+
