Counting Email Addresses By Domain in MySQL

1 Apr 2009 — eric

Hello there! If you are new here, you might want to subscribe to the RSS feed for updates on this topic.

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 |
+----------------+---------+

Be a loud mouth:

Posted in MySQL. Tags: MySQL, statistics. View Comments

View Comments to “Counting Email Addresses By Domain in MySQL”

soumen Says:
8 Jan 2010 at 09:33
i want to update my table for a domain as

update table set field=CONCAT(SUBSTRING_INDEX(email, '@', 1),'@piaggio.co.in') where domain='mydomain'

But isn't working .

pls. help
Eric Lubow Says:
8 Jan 2010 at 12:08
What does isn't working mean? Is it updating the wrong rows? Is it updating
the correct rows with the wrong data? Are there multiple '@' signs? Please
be more specific.
Soumen Says:
8 Jan 2010 at 13:00
i used the query
update newsletter_email set email=CONCAT(SUBSTRING_INDEX(email, '@', 1),'@piaggio.co.in'),email_domain='piaggio.co.in'
where email_domain like '%pgvl%'

to update all rows having email field with pgvl tld domains to
emil@piaggio.co.in

But mysql gave the error

Duplicate entry 'kpradeep@piaggio.co.in' for key 2

although there is only one email as it is a uniue key.
Frank C. Tannehill Says:
21 Jan 2010 at 14:25
Statistically speaking, Yahoo wins by far! Look just how many emails come from this service. I thought Gmail was gaining the too famous Yahoo but I was wrong.
_______________
Frank C. Tannehill
domain name
Eric Lubow Says:
21 Jan 2010 at 16:09
This was just a database made up of arbitrary data from a much larger database of email addresses. It doesn't really speak to anything other than the RANDOM() function of MySQL. I don't know what the Gmail vs. Yahoo! stats are, but they are probably better than the table above might lead one to believe .
Jose Says:
16 Mar 2010 at 11:36
THANK YOU SO MUCH!!!
jrobgood Says:
15 Apr 2010 at 22:46
You have to take into consideration that Google dropped the barriers to entry for custom domain names in its free services. So if you did an MX lookup on all addresses, and counted the ones that are handled by gmail.com, I think you'd be surprised.