计算不同值的出现次数 [英] Count occurrences of distinct values

问题描述

我正在尝试查找一个 MySQL 查询，该查询将显示一个 ID 值在一年内出现的次数.

I am trying to find a MySQL query that will display the number of occurrences of an ID value within a year.

表格:

a_id      year
----      ----
1        2010
1        2011
1        2012
1        2012
1        2013
1        2014
1        2015
2        2010
2        2011
2        2013
2        2014
2        2014
2        2015
3        2010
3        2010
3        2011

预期输出:

a_id    year    occurrences
----    -----   -----------
1       2010        1
1       2011        1
1       2012        2
1       2013        1
1       2014        1
1       2015        1
2       2010        1
2       2011        1
2       2013        1
2       2014        2
2       2015        1
3       2010        2
3       2011        1

我正在尝试使用类似于以下 sql 查询的内容，但它给我的结果与预期的输出完全不同.这是我挣扎的第三列.

I'm trying with the something along the lines of following sql query, but it gives me nothing like the expected output. It's the 3rd column im struggling with.

SELECT a__id, year, count(distinct a_id) as occurrences
FROM table1
GROUP by year
ORDER by a_id

如何创建第三列?

推荐答案

由于您按 a_id 和 year 进行分组，您当然只能获得 1 个不同的组值.只需将 count(distinct a_id) 更改为 count(*).

Scince you are grouping by a_id and year you of course get only 1 distinct value for group. Simply change count(distinct a_id) to count(*).

例如你得到组:

1        2012
1        2012

注意该组中不同的 a_id 值为 1.但是您想要对组中的所有行进行计数.使用 distinct，您将在所有组中出现 1.

Notice in this group distinct a_id values is 1. But you want count of all rows in group. With distinct you will get 1 as occurence in all groups.

编辑:

好的，我错过了您仅按 year 分组，因此您也应该按 a_id 分组.其余的答案保持原样.所以你最终得到:

Ok, I have missed that you are grouping only by year, so you should group by a_id also. The rest of the answer stays as is. So you end up with:

SELECT a__id, year, count(*) as occurrences
FROM table1
GROUP by a__id, year

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