Calculating amount/percentage of unique domains in email addresses with SQL
March 22nd, 2010
Hello, this is my first technical english post so do not read it very criticaly. I want to describe interesting task with SQL which I faced with.
I have a table with next columns: id, email, etc.
| id | etc | |
|---|---|---|
| 1 | example@yahoo.com | … |
| 2 | example@gmail.com | … |
| … | … | … |
| 90000 | example@hotmail.com | … |
I need to calculate quantity of each e-mail domain, calculate percent of domain and sort this information.
Solution:
SELECT domain, total, (total / emailscount) * 100 AS domainpercentage FROM ( SELECT SUBSTRING_INDEX(email, '@', -1) AS domain, COUNT(contacts_list.id) AS total, ( SELECT COUNT(cl.id) FROM contacts_list AS cl ) AS emailscount FROM contacts_list GROUP BY domain ORDER BY total DESC ) AS result
Explanation:
At first I select all domains and quantity of their entries.
SELECT SUBSTRING_INDEX(email, '@', -1) AS domain, COUNT(contacts_list.id) AS total, ( SELECT COUNT(cl.id) FROM contacts_list AS cl ) AS emailscount FROM contacts_list GROUP BY domain ORDER BY total DESC
Also I select amount of all entries in my table via inner query.
( SELECT COUNT(cl.id) FROM contacts_list AS cl ) AS emailscount
Then I wrap my query where I select inner`s query results and calculate percentage of each domain.
SELECT domain, total, (total / emailscount) * 100 AS domainpercentage FROM (... ) AS result
