MySQL Select the Number of Entries that Occur "N" times

mysql, sql

Solution

Try:

SELECT numberOfEntries, count(*) FROM (
  SELECT email, count(*) AS numberOfEntries
    FROM entries
   GROUP BY email
)
GROUP BY numberOfEntries

You can add `HAVING` clause to inner select to restrict counts for number of entries returned to 1,2,3 or whatever else.

Problem

I've collected a number of entries in a table for a sweepstakes. I've been asked by the client to provide: - The number of unique entries - The number of entries that occur twice. - The number of entries that occur three times. I'm able to determine the number of unique entries, but not sure how to pull out the number of entries that occur a specific number of times. I tried something like this: SELECT email, count(email) AS NumberOfEntries FROM entries GROUP BY NumberOfEntries That gives the error: Can't group on 'NumberOfEntries' I'm hoping to see something like this: NumberOfEntries / Total 1 / 1,000 (Meaning 1,000 people entered once and only once) 2 / 1,300 (Meaning 1,300 people entered exactly twice) Thanks for any help!

Original source