SQL 计算每个唯一值的出现次数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13235981/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count number of occurrences for each unique value
提问by James Elder
Basically I have a table similar to this:
基本上我有一个类似于这个的表：
time.....activities.....length
13:00........3.............1
13:15........2.............2
13:00........3.............2
13:30........1.............1
13:45........2.............3
13:15........5.............1
13:45........1.............3
13:15........3.............1
13:45........3.............2
13:45........1.............1
13:15........3.............3
A couple of notes:
一些注意事项：
- Activities can be between 1 and 5
- Length can be between 1 and 3
- 活动可以在 1 到 5 之间
- 长度可以在 1 到 3 之间
The query should return:
查询应返回：
time........count
13:00.........2
13:15.........2
13:30.........0
13:45.........1
Basically for each unique time I want a count of the number of rows where the activities value is 3.
基本上，对于每个唯一的时间，我想要计算活动值为 3 的行数。
So then I can say:
那么我可以说：
At 13:00 there were X amount of activity 3s.
At 13:45 there were Y amount of activity 3s.
Then I want a count for activity 1s,2s,4s and 5s. so I can plot the distribution for each unique time.
然后我想要活动 1s、2s、4s 和 5s 的计数。所以我可以绘制每个独特时间的分布。
回答by Jamey Sharp
Yes, you can use group by:
是的，您可以使用group by：
select time, activities, count(*) from table group by time, activities;
回答by RedFilter
select time, coalesce(count(case when activities = 3 then 1 end), 0) as count
from MyTable
group by time
Output:
输出：
| TIME | COUNT |
-----------------
| 13:00 | 2 |
| 13:15 | 2 |
| 13:30 | 0 |
| 13:45 | 1 |
If you want to count all the activities in one query, you can do:
如果要计算一个查询中的所有活动，可以执行以下操作：
select time,
coalesce(count(case when activities = 1 then 1 end), 0) as count1,
coalesce(count(case when activities = 2 then 1 end), 0) as count2,
coalesce(count(case when activities = 3 then 1 end), 0) as count3,
coalesce(count(case when activities = 4 then 1 end), 0) as count4,
coalesce(count(case when activities = 5 then 1 end), 0) as count5
from MyTable
group by time
The advantage of this over grouping by activities, is that it will return a count of 0 even if there are no activites of that type for that time segment.
与按活动分组相比，这样做的优势在于，即使该时间段没有该类型的活动，它也会返回 0 计数。
Of course, this will not return rows for time segments with no activities of any type. If you need that, you'll need to use a left join with table that lists all the possible time segments.
当然，这不会返回没有任何类型活动的时间段的行。如果需要，您需要使用左连接与列出所有可能的时间段的表。
回答by Modika
If i am understanding your question, would this work? (you will have to replace with your actual column and table names)
如果我理解你的问题，这行得通吗？（您必须用实际的列名和表名替换）
SELECT time_col, COUNT(time_col) As Count
FROM time_table
GROUP BY time_col
WHERE activity_col = 3
回答by geetha
You should change the query to:
您应该将查询更改为：
SELECT time_col, COUNT(time_col) As Count
FROM time_table
WHERE activity_col = 3
GROUP BY time_col
This vl works correctly.
这个 vl 工作正常。
