select t.id , t.name , t.type , total = case when len(rtrim(t.a)=1) then (select sum(total) from [in] where name like '%' + t.name + '%') else 0 end from [in] t

create table [in](id int,name varchar(10) , type varchar(10) , total int)
insert into [in] values(1 ,  'a' ,  'aa' ,    0) 
insert into [in] values(2 ,  'a1',  'aa' ,    2) 
insert into [in] values(3 ,  'a2',  'aa' ,    3) 
insert into [in] values(4 ,  'd' ,  'bb' ,    0) 
insert into [in] values(5 ,  'd1',  'bb' ,    6) 
insert into [in] values(6 ,  'd2',  'bb' ,    2) 
go

select t.id , t.name , t.type , total = (case when len(rtrim(t.name))=1 then (select sum(total) from [in] where name like '%' + t.name + '%') else 0 end) from [in] t
/*
id          name       type       total       
----------- ---------- ---------- ----------- 
1           a          aa         5
2           a1         aa         0
3           a2         aa         0
4           d          bb         8
5           d1         bb         0
6           d2         bb         0

（所影响的行数为 6 行）
*/

select t.id , t.name , t.type , total = (case when len(rtrim(t.name))=1 then (select sum(total) from [in] where type = t.type) else 0 end) from [in] t
/*
id          name       type       total       
----------- ---------- ---------- ----------- 
1           a          aa         5
2           a1         aa         0
3           a2         aa         0
4           d          bb         8
5           d1         bb         0
6           d2         bb         0

（所影响的行数为 6 行）
*/

drop table [in]

select tab.id,tab.name,tab.[type],isnull(tab2.total,0) as total from 
tab
left outer join
(
    select a.id,a.[type],b.total from
    (
        select [type],min(id) as id from tab group by [type]
    )as a
    inner join
    (
        select [type],sum(total) as total from tab
        group by [type]
    ) as b
    on a.[type] = b.[type]
) as tab2
on tab.id = tab2.id and tab.[type] = tab2.[type]

insert into tout
select * from tin
where total > 0
union all
select a.id, a.name, b.type, b.total from tin a
inner join 
(select TYPE, SUM(total) as total
from tin
group by type) b
on a.type = b.type
where a.total = 0
order by id

select * from tin
where total > 0
union all
select a.id, a.name, b.type, b.total from tin a
inner join 
(select TYPE, SUM(total) as total
from tin
group by type) b
on a.type = b.type
where a.total = 0
order by id

/*
id          name       type       total
----------- ---------- ---------- -----------
1           a          aa         5
2           a1         aa         2
3           a2         aa         3
4           d          bb         8
5           d1         bb         6
6           d2         bb         2

(6 row(s) affected)*/

create table [in](id int,name varchar(10) , type varchar(10) , total int)
insert into [in] values(1 ,  'a' ,  'aa' ,    0) 
insert into [in] values(2 ,  'a1',  'aa' ,    2) 
insert into [in] values(3 ,  'a2',  'aa' ,    3) 
insert into [in] values(4 ,  'd' ,  'bb' ,    0) 
insert into [in] values(5 ,  'd1',  'bb' ,    6) 
insert into [in] values(6 ,  'd2',  'bb' ,    2) 
go

select t.id , t.name , t.type , total = (case when len(rtrim(t.name))=1 then (select sum(total) from [in] where name like '%' + t.name + '%') else total end) from [in] t
/*
id          name       type       total       
----------- ---------- ---------- ----------- 
1           a          aa         5
2           a1         aa         2
3           a2         aa         3
4           d          bb         8
5           d1         bb         6
6           d2         bb         2

（所影响的行数为 6 行）
*/

select t.id , t.name , t.type , total = (case when len(rtrim(t.name))=1 then (select sum(total) from [in] where type = t.type) else total end) from [in] t
/*
id          name       type       total       
----------- ---------- ---------- ----------- 
1           a          aa         5
2           a1         aa         2
3           a2         aa         3
4           d          bb         8
5           d1         bb         6
6           d2         bb         2

（所影响的行数为 6 行）
*/

drop table [in]

select id, name, type,total from tin
where total > 0
union all
select a.id, a.name, b.type, b.total from tin a
inner join 
(select TYPE, SUM(total) as total
from tin
group by type) b
on a.type = b.type
where a.total = 0
order by id

select id, name, type,total, 其他字段 from tin
where total > 0
union all
select a.id, a.name, b.type, b.total, 其他字段 from tin a
inner join 
(select TYPE, SUM(total) as total
from tin
group by type) b
on a.type = b.type
where a.total = 0
order by id