set nocount on

create table #tmp (field_a varchar(20) not null , field_b int, field_c varchar(30))

insert #tmp select 'SQL Team', 0 , 'Website'
insert #tmp select 'remarket' , 10 , 'Video Killed the Radio Star'
insert #tmp select 'CSCO' , 15 , 'CISCO Systems'
insert #tmp select 'remarket' , 55 , 'It''s the shoes, Money!'

print '** original select **'
select * from #tmp

print ''
print '** Select with SUM **'
select *
, sum_field = case 
when field_a = 'remarket' 
then (select sum(field_b) 
      from #tmp 
      where field_a = 'remarket') else NULL end
from #tmp


-- drop table #tmp

/* here are the results */
** original select **
field_a              field_b     field_c                        
-------------------- ----------- ------------------------------ 
SQL Team             0           Website
remarket             10          Video Killed the Radio Star
CSCO                 15          CISCO Systems
remarket             55          It's the shoes, Money!

 
** Select with SUM **
field_a              field_b     field_c                        sum_field   
-------------------- ----------- ------------------------------ ----------- 
SQL Team             0           Website                        NULL
remarket             10          Video Killed the Radio Star    65
CSCO                 15          CISCO Systems                  NULL
remarket             55          It's the shoes, Money!         65