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

Question

Hello All:

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!

Answer 1

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.

Answer 2

How about this:

SELECT email, count(email) as NumberOfEntries
FROM entries
GROUP BY email

will give you a list of email and number of entries (the GROUP BY should list all non-aggregate columns). If you want to limit the selection to only those emails with 2 or 3 entries you need to use a having clause.

SELECT email, count(email) as NumberOfEntries
FROM entries
GROUP BY email
HAVING NumberOfEntries = 2

Or, to get a count of the number of each entry, you can wrap that in another query like:

SELECT NumberOfEntries, COUNT(*) AS NumberOfEmails
FROM (SELECT email, count(email) as NumberOfEntries
    FROM entries
    GROUP BY email) AS x
GROUP BY NumberOfEntries
ORDER BY NumberOfEntries ASC

This should give you number of entries, and a count of how many people have that number of entries (listed from 1 entry up)

Answer 3

Brenton,

How about including a column that will further sort the results into unique records. For example, if i need to pull records with unique social security numbers (SSN) in the below sql, then how would i do that :

SELECT email, count(email) as NumberOfEntries FROM entries GROUP BY email HAVING NumberOfEntries = 2

I tried putting DINTINCT ssn before "email, count(email)" but that aint helping. I am not too good at sql.

Thanks