Question

我在一张桌子上收集了几处 entries子。客户要求我提供:

The number of unique entries
The number of entries that occur twice.
The number of entries that occur three times.

我能够确定独一无二的条目数目,但无法确定如何删除特定时期的条目数目。我试图这样做:

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

造成错误: Can t group on NumberOftries

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)

感谢任何帮助!

Answer 1

Try:

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

页: 1 HAVING 内部选择条款,以限制被退回到1,2,3条或其它任何内容的条目数目。

Answer 2

如何做到这一点:

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

将向您提供一份电子邮件清单和条目编号(小组应列出所有非合计栏目)。如果你只希望把选择限制在有2个或3个条目的电子邮件上,那么你需要使用一个条款。

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

或者,为了计算每个条目的数量,你可以总结一下,在另一个问询中,例如:

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

这应当给你更多的条目,并计算有多少人(从1个条目中除名)的条目。

友情链接