Calculating amount/percentage of unique domains in email addresses with SQL

Calculating amount/percentage of unique domains in email addresses with SQL

March 22nd, 2010

Filed under: Advice,Tip — Zinchenko Oleg @ 5:16 pm

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

Read Comments (0)