oracle sql 组数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3856723/
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 count
提问by mitsky
SELECT a,b,count(*)
FROM t
GROUP BY rollup(a,b)
result:
结果：
a1, b1, 10
a1, b2, 90
a1, , 100
i need:
我需要：
a1, b1, 10, 100
a1, b2, 90, 100
how?
如何？
回答by Rob van Wijk
This table simulates your situation:
此表模拟您的情况：
SQL> create table t (a,b)
2 as
3 select 'a1', 'b1'
4 from dual
5 connect by level <= 10
6 union all
7 select 'a1', 'b2'
8 from dual
9 connect by level <= 90
10 /
Table created.
Your result with only three rows misses the grand total, so that's a little inaccurate: rollup(a,b) results in 3 grouping sets with 4 rows.
只有三行的结果错过了总计，所以这有点不准确： rollup(a,b) 导致 3 个分组集有 4 行。
SQL> select a
2 , b
3 , count(*)
4 from t
5 group by rollup(a,b)
6 /
A B COUNT(*)
-- -- ----------
a1 b1 10
a1 b2 90
a1 100
100
4 rows selected.
With a regular group by and an analytic function on top, you can achieve your desired result set:
使用常规的 group by 和顶部的分析函数，您可以获得所需的结果集：
SQL> select a
2 , b
3 , count(*)
4 , sum(count(*)) over (partition by a)
5 from t
6 group by a
7 , b
8 /
A B COUNT(*) SUM(COUNT(*))OVER(PARTITIONBYA)
-- -- ---------- -------------------------------
a1 b2 90 100
a1 b1 10 100
2 rows selected.
Regards, Rob.
问候，罗伯。
回答by OMG Ponies
Use:
用：
SELECT t.a,
t.b,
COUNT(*),
(SELECT COUNT(*)
FROM YOUR_TABLE
GROUP BY t.a)
FROM YOUR_TABLE t
GROUP BY t.a, t.b
