表a如下：
aa bb
a 100
b 200
a 100
a 50
b 100
b 90
c 50
c 40
.
.
.

怎样写sql语句输出如下结果
aa bb
a 50
a 100
a 100
合计 250
b 90
b 100
b 200
合计 390
.
.
.
尽量用一句分组语句。

找帮助with rollup

select case when(bb=bb) then aa
WHEN (GROUPING(aa) = 1) THEN '总计'
else '合计' end as aa,
sum(num) as bb
from 表名
group by aa,bb
with rollup

declare @a table(aa varchar(1),bb int)
insert into @a select 'a',100
union all select 'b',200
union all select 'a',100
union all select 'a',50
union all select 'b',100
union all select 'b',90
union all select 'c',50
union all select 'c',40

select aa=case when grouping(bb)=1 then '合计' else aa end,sum(bb) as bb from @a group by aa,bb with rollup having grouping(aa)<>1