select id,name,type , sum( case when total <>0 then total else 1 end ) as total from tb group by id,name,type

select t.id , t.name , t.type , total = (case when len(trim(t.name)) = 1 then (select sum(total) from tb where id = t.id and type = t.type) else total end) from tb t

declare @tb table (id int, cname nvarchar(20), ctype nvarchar(20), total int)
insert into @tb
select 1,   'a',   'aa', 0   
union all select 1   ,'a1',  'aa',     2 
union all select 1   ,'a2',  'aa',     0 
union all select 1   ,'b',   'bb',     0 
union all select 1   ,'b1',  'bb',     1 
union all select 1   ,'b2',  'bb',     2 
union all select 2   ,'a',  'aa',     0 
union all select 2   ,'a1',  'aa',     6 
union all select 2   ,'a2',  'aa',     4 
union all select 2   ,'b',   'bb',     0 
union all select 2   ,'b1',  'bb',     5 
union all select 2   ,'b2',  'bb',     4 
union all select 3   ,'a',   'aa',     0 
union all select 3   ,'a1',  'aa',     3 
union all select 3   ,'a2',  'aa',     0 
union all select 3   ,'b',   'bb',     0 
union all select 3   ,'b1',  'bb',     2 
union all select 3   ,'b2',  'bb',     4

select a.id,a.cname,case when (b.total is null or b.total =0 ) then a.total else b.total end as total from @tb a
left join ( select id, left(cname,1) as cname, sum(total) as total from @tb group by id, left(cname,1)) b on a.id = b.id and a.cname = b.cname
/*

id          cname                total
----------- -------------------- -----------
1           a                    2
1           a1                   2
1           a2                   0
1           b                    3
1           b1                   1
1           b2                   2
2           a                    10
2           a1                   6
2           a2                   4
2           b                    9
2           b1                   5
2           b2                   4
3           a                    3
3           a1                   3
3           a2                   0
3           b                    6
3           b1                   2
3           b2                   4

(18 row(s) affected)


*/

create table tbx(id int, name varchar(5), type varchar(5), total  int)
insert into tbx select 1, 'a' ,'aa', 0  union 
select 1 ,'a1','aa',   2 union 
select 1 ,'a2','aa',   0 union 
select 1 ,'b' ,'bb',   0 union 
select 1 ,'b1','bb',   1 union 
select 1 ,'b2','bb',   2 union 
select 2 ,'a' ,'aa',   0 union 
select 2 ,'a1','aa',   6 union 
select 2 ,'a2','aa',   4 union 
select 2 ,'b' ,'bb',   0 union 
select 2 ,'b1','bb',   5 union 
select 2 ,'b2','bb',   4 union 
select 3 ,'a' ,'aa',   0 union 
select 3 ,'a1','aa',   3 union 
select 3 ,'a2','aa',   0 union 
select 3 ,'b' ,'bb',   0 union 
select 3 ,'b1','bb',   2 union 
select 3 ,'b2','bb',   4 
go
select id,name,type,t=(case when len(name)=1 then (select sum(total) from tbx where id=a.id and type=a.type group by id,type) else total end) from tbx a
/*
id          name  type  t
----------- ----- ----- -----------
1           a     aa    2
1           a1    aa    2
1           a2    aa    0
1           b     bb    3
1           b1    bb    1
1           b2    bb    2
2           a     aa    10
2           a1    aa    6
2           a2    aa    4
2           b     bb    9
2           b1    bb    5
2           b2    bb    4
3           a     aa    3
3           a1    aa    3
3           a2    aa    0
3           b     bb    6
3           b1    bb    2
3           b2    bb    4
*/

select *,total=case when patindex('%[0-9]%',cname)<=0 then 
(select sum(total) from 表名 where id=a.id and ctype=a.ctype)
else total end 
 from 表名 a

/******************************************/
/*回复：代码20080512001 总：00000000001    */
/*主题：分组求和                           */
/*作者：二等草                             */
/******************************************/

/************例子数据 begin***********************/
declare @tb table (id int, cname varchar(20), ctype varchar(20), total int)
insert into @tb
select 1,   'a',   'aa', 0   
union all select 1   ,'a1',  'aa',     2 
union all select 1   ,'a2',  'aa',     0 
union all select 1   ,'b',   'bb',     0 
union all select 1   ,'b1',  'bb',     1 
union all select 1   ,'b2',  'bb',     2 
union all select 2   ,'a',  'aa',     0 
union all select 2   ,'a1',  'aa',     6 
union all select 2   ,'a2',  'aa',     4 
union all select 2   ,'b',   'bb',     0 
union all select 2   ,'b1',  'bb',     5 
union all select 2   ,'b2',  'bb',     4 
union all select 3   ,'a',   'aa',     0 
union all select 3   ,'a1',  'aa',     3 
union all select 3   ,'a2',  'aa',     0 
union all select 3   ,'b',   'bb',     0 
union all select 3   ,'b1',  'bb',     2 
union all select 3   ,'b2',  'bb',     4
/************例子数据 end***********************/

/******************代码 begin*******************/

select id,cname,ctype
,total = case when len(cname)> 1 then total 
    else (select sum(total) from @tb where id =a.id and ctype = a.ctype) end
from @tb a

/******************代码 end*********************/

/******************结果 begin********************
id          cname                ctype                total       
----------- -------------------- -------------------- ----------- 
1           a                    aa                   2
1           a1                   aa                   2
1           a2                   aa                   0
1           b                    bb                   3
1           b1                   bb                   1
1           b2                   bb                   2
2           a                    aa                   10
2           a1                   aa                   6
2           a2                   aa                   4
2           b                    bb                   9
2           b1                   bb                   5
2           b2                   bb                   4
3           a                    aa                   3
3           a1                   aa                   3
3           a2                   aa                   0
3           b                    bb                   6
3           b1                   bb                   2
3           b2                   bb                   4
*******************结果 end**********************/

/******************清除**************************/