Oracle SQL 按单个字段分组并计算分组的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14378475/
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
Oracle SQL group by single field and count the grouped rows
提问by ServerBloke
Consider this table some_table:
考虑这个表some_table：
+--------+----------+---------------------+-------+
| id | other_id | date_value | value |
+--------+----------+---------------------+-------+
| 1 | 1 | 2011-04-20 21:03:05 | 104 |
| 2 | 1 | 2011-04-20 21:03:04 | 229 |
| 3 | 3 | 2011-04-20 21:03:03 | 130 |
| 4 | 1 | 2011-04-20 21:02:09 | 97 |
| 5 | 2 | 2011-04-20 21:02:08 | 65 |
| 6 | 3 | 2011-04-20 21:02:07 | 101 |
| ... | ... | ... | ... |
+--------+----------+---------------------+-------+
I want to select and group by the other_id, so that I only get unique other_ids. This query works (credit @MichaelPakhantsov):
我想按 选择和分组other_id，这样我只能得到唯一的other_ids。此查询有效（信用@MichaelPakhantsov）：
select id, other_id, date_value, value from
(
SELECT id, other_id, date_value, value,
ROW_NUMBER() OVER (partition by other_id order BY Date_Value desc) r
FROM some_table
)
where r = 1
How can I get the same result, but with a count of how many rows were grouped, for each other_id. The desired result would look like:
我怎样才能得到相同的结果，但计算每个other_id. 所需的结果如下所示：
+--------+----------+---------------------+-------+-------+
| id | other_id | date_value | value | count |
+--------+----------+---------------------+-------+-------+
| 1 | 1 | 2011-04-20 21:03:05 | 104 | 3 |
| 5 | 2 | 2011-04-20 21:02:08 | 65 | 2 |
| 3 | 3 | 2011-04-20 21:03:03 | 130 | 2 |
+--------+----------+---------------------+-------+-------+
I've tried using COUNT(other_id)in both the inner and outer selects but it produces this error:
我已经尝试COUNT(other_id)在内部和外部选择中使用，但它产生了这个错误：
ORA-00937: not a single-group group function
ORA-00937: 不是单组组函数
Note: similar to this question(example table and answer taken from there) but that question doesn't give a count of the collapsed rows.
注意：类似于这个问题（示例表和从那里获取的答案），但该问题没有给出折叠行的计数。
回答by DazzaL
add a
添加一个
count(*) OVER (partition by other_id) cnt
to the inner query
到内部查询
select id, other_id, date_value, value, cnt from
(
SELECT id, other_id, date_value, value,
ROW_NUMBER() OVER (partition by other_id order BY Date_Value desc) r,
count(*) OVER (partition by other_id) cnt
FROM some_table
)
where r = 1
