nix, shell, perl, php, mysql and mac os x tips and tricks

Thursday, April 1, 2010

Create report on top domains represented in a table of e-mail addresses

Shows the count for each domain, and the percentage of the total each domain represents. For opt-in e-mail addresses only. Excludes any domain that has less than 100 associated e-mails (therefore showing only the major ones).

SELECT 
 COUNT(c.email) as count, 
 CONCAT(FORMAT((COUNT(c.email)/t.TOTAL)*100, 2),"%") AS percent,
 SUBSTRING_INDEX(c.email,'@',-1) AS domain 
FROM email_table AS c,  
 (SELECT count(email) as TOTAL 
 FROM email_table 
 WHERE optin="yes")
 AS t
WHERE optin = "yes" 
GROUP BY domain 
HAVING COUNT > 100
ORDER BY COUNT DESC