汇总统计，如何自动计算统计结果的和

[sunshine320]

我现在在做分类汇总统计，
例如：Select name,count(id) num from table1 group by name
结果为：
name num
aaaa 2
bbbb 3
cccc 7

我想获取汇总结果的和，就是自动计算累加值，生成以下结果：
name num
aaaa 2
bbbb 3
cccc 7
sum 12

不知道怎么写SQL语句？

[iqlife]

Select name,count(id) num
from table1
group by CUBE(name)
ORDER BY NAME

[Adebayor]

Select name,count(id) num from table1 group by name
union all
select 'sum',count(id) from table1
;

[碧水幽幽泉]

楼上两位都对，但是1楼好些！

[xman_78tom]

Select (case grouping(name) when 1 then 'sum' else name end) name, count(id) num 
from table1 group by cube(name) order by num

[sylar09]

新手请教：2楼的group by CUBE(name)怎么理解？

[tangren]

with tt as(
  select 1  id, 'a' name from dual union all
  select 2  id, 'a' name from dual union all
  select 3  id, 'b' name from dual union all
  select 4  id, 'b' name from dual union all
  select 5  id, 'b' name from dual union all
  select 6  id, 'c' name from dual union all
  select 7  id, 'c' name from dual union all
  select 8  id, 'c' name from dual union all
  select 9  id, 'c' name from dual union all
  select 10 id, 'c' name from dual union all
  select 11 id, 'c' name from dual union all
  select 12 id, 'c' name from dual)
SELECT nvl(NAME, 'sum') NAME, COUNT(id) num FROM tt GROUP BY ROLLUP(NAME);

[siakang]

楼上较严谨些

[Leshami]

[Quote=引用 1 楼 java3344520 的回复:]

Select name,count(id) num
from table1
group by CUBE(name)
ORDER BY NAME
[/Quote]
up

[guokooo]

2楼好像很厉害哦

[chenhuizhouhb]

cube是oracle group by的扩展用法，rollup，和grouping，having都在其中，解决你的问题最好用这个
Select name,count(id) num
from table1
group by rollup(name)
order by name

[chenhuizhouhb]

说明 tangren正解