分组求出合计的问题？？

[zzxiaoma]

表a

ccode kind_big_bm
0001 01
0001 01
0001 02
0002 02
0003 01
0003 03

根据ccode和kind_big_bm分组求的数量结果为
0001 01 2
0002 02 1
0003 01 1
0003 03 1
现在需要在下面加个合计
结果
0001 01 2
0002 02 1
0003 01 1
0003 03 1
合计 5
这个应该怎么做

[-狙击手-]

with cube ,,,,,,,,,,,,,,,,,,,,,,,,,,,

[kk19840210]

  select ccode,kind_big_bm,hj=count(1) from tba group by ccode,kind_big_bm
  union all
  select '合计','',count(1) from tba

[liangCK]

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ccode varchar(4),kind_big_bm varchar(2))
insert into #T
select '0001','01' union all
select '0001','01' union all
select '0001','02' union all
select '0002','02' union all
select '0003','01' union all
select '0003','03'

select ccode,kind_big_bm,COUNT(*) cnt
from #T
group by ccode,kind_big_bm
union all
select N'合计' as ccode,'',COUNT(*) cnt
from #T

/*
ccode kind_big_bm cnt
----- ----------- -----------
0001  01          2
0003  01          1
0001  02          1
0002  02          1
0003  03          1
合计                6

(6 行受影响)
*/

[liangCK]

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ccode varchar(4),kind_big_bm varchar(2))
insert into #T
select '0001','01' union all
select '0001','01' union all
select '0001','02' union all
select '0002','02' union all
select '0003','01' union all
select '0003','03'

select isnull(ccode,'合计') ccode,kind_big_bm,COUNT(*) cnt
from #T
group by ccode,kind_big_bm
with rollup
having GROUPING(ccode)=1 or GROUPING(kind_big_bm)=0

/*
/*
ccode kind_big_bm cnt
----- ----------- -----------
0001  01          2
0003  01          1
0001  02          1
0002  02          1
0003  03          1
合计                6

(6 行受影响)
*/
*/

[zzxiaoma]

如果写成
select b.ccode,kind_big_bm,count(1) as num from sys_kind_small a,emergency_regi b,code_login_unit c
where a.kind_small_bm=b.kind_small_bm and b.ccode=c.ccode and
(c.ccode like '4107%' and c.ilevel=3 or b.ccode='4107000000')
group by b.ccode,kind_big_bm
union all
select '合计','',count(1) from sys_kind_small a,emergency_regi b,code_login_unit c
where a.kind_small_bm=b.kind_small_bm and b.ccode=c.ccode and
(c.ccode like '4107%' and c.ilevel=3 or b.ccode='4107000000')

不能按ccode排序

[kk19840210]

create table #T (ccode varchar(4),kind_big_bm varchar(2))
insert into #T
select '0001','01' union all
select '0001','01' union all
select '0001','02' union all
select '0002','02' union all
select '0003','01' union all
select '0003','03'


select ccode,kind_big_bm,hj=count(1) from #t group by ccode,kind_big_bm
  union all
  select '合计','',count(1) from #t
order by ccode,kind_big_bm

ccode kind_big_bm hj
----- ----------- -----------
0001  01          2
0001  02          1
0002  02          1
0003  01          1
0003  03          1
合计                6

(6 行受影响)