Query for frequency of e-mail addresses in a field

Posted on

Question :

Newbie here. I know the answer is out there, but lack the vocabulary to ask the right question…

I am looking for a MYSQL query which gives me the frequency of addresses in the field emailaddress. I would like to know how many are unique, how many occur twice, three times etc.

Answer :

I think you want a “count of counts”, which can be done with 2 GROUP BY, once to find how many times an emailaddress has been used:

    SELECT emailaddress, COUNT(*) AS frequency
    FROM tablename
    GROUP BY emailaddress ;

and then enclosing the above in a derived table and doing another GROUP BY to find how many emailaddresses were used once, twice, thrice, etc…:

SELECT frequency, COUNT(*) AS emailadresses
FROM
  ( SELECT COUNT(*) AS frequency           -- we don't need email any more
    FROM tablename
    GROUP BY emailaddress
  ) AS x 
GROUP BY frequency ;

Minor mysql quirk. If you want:

GROUP BY frequency 
ORDER BY frequency DESC ;

it can be abbreviated (and it will be a bit more efficient) in MySQL as:

GROUP BY frequency DESC ;

the ones that occurs once are of course unique, but if you want to count let’s say all emails that occurs once, twice, thrice and so on, you might have to use a subquery as ypercube has explained.

SELECT DISTINCT email, COUNT(email) FROM email_table GROUP BY email;

Leave a Reply

Your email address will not be published.