计算另一个计数结果的出现次数 [英] Count the amount of occurrences of a another count result

问题描述

我们有一个名为entries的表，该表按日期存储用户信息.每天允许用户进入数据库一次.一些示例数据:

We have a table called entries which stores user information against a date. Users are allowed to enter the database once per day. Some example data:

+----+------------------+----------+
| id |      email       |   date   |
+----+------------------+----------+
|  1 | [e-mail] | 04.09.13 |
|  2 | [e-mail]  | 04.09.13 |
|  3 | [e-mail] | 05.09.13 |
+----+------------------+----------+

我需要计算出数据库中具有相同电子邮件的X条目有多少次.例如，以上数据的结果应如下所示，其中我们有1个实例的1个条目和1个实例的2个条目:

I need to work out how many times there are X entries with the same email in the database. For example, the result should look like this for the above data, where we have 1 instance of one entry and 1 instance of 2 entries:

+---------------+-------+
| times_entered | total |
+---------------+-------+
|             1 |     1 |
|             2 |     1 |
+---------------+-------+

我已经尝试了一些方法，但是我能得到的最远的结果是对找到每个电子邮件地址的次数进行计数.看来我从这里需要做的就是整理这些结果并在这些组上执行另一个COUNT来获取总数，但是我不确定如何做到这一点.

I've tried a few things but the furthest I have been able to get is getting a count of the amount of times each email address was found. It seems like all I need to do from here is collate those results and perform another COUNT on those groups to get the totals, but I'm unsure of how I can do that.

推荐答案

以下内容将获取每封电子邮件的记录数:

The following will get the number of records per email:

SELECT COUNT(1) AS times_entered, email
FROM entries
GROUP BY email

因此，使用此查询作为派生表，我们可以按记录数分组以获取计数(我们不需要在子查询中选择email列，因为我们不在乎):

Therefore, using this query as a derived table, we can group by the number of records to get the count (we do not need to select the email column in the subquery because we don't care about it):

SELECT times_entered, COUNT(1) AS total
FROM
(
    SELECT COUNT(1) AS times_entered
    FROM entries
    GROUP BY email
) x
GROUP BY times_entered

SQL Fiddle演示

SQL Fiddle演示，数据集稍大

这篇关于计算另一个计数结果的出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！