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.
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;