views:

173

answers:

2

I'd like to get a better idea of what domains my customers are using. I could easily do this in PHP by explodeing 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.

A: 

How about something like

SELECT COUNT(DISTINCT [what you want])
FROM MyTable

COUNT(DISTINCT expr,[expr...])

astander
i'm not sure what the expression would be to separate the parts of the email address into user and domain portions.
Ian
+3  A: 

You would have to do something like this:

SELECT substring_index(email, '@', -1), COUNT(*)
FROM table
GROUP BY substring_index(email, '@', -1);
WoLpH
very nice, thanks! i added a sort on the count(*) and i get exactly the results i need.
Ian