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
Thursday, April 1, 2010
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).