urgent- difference sum within a colomn based on other colomn

[calvinkwoo3000]

Hi i have a table as below

year month income source
_______________ ____________
2008 06 100 source1
2008 06 80 source2
2008 06 200 source1
2008 06 100 source2

my output is

year month income
_______________ ____
2008 06 120 (sum(rev) source1 - sum(rev) source2)

may i know what is the SQL query statement can do that????

[ck9663]

Try this:

declare @IncomeTable table (yr int, mon varchar(2), income money, src varchar(10))

insert into @IncomeTable values(2008, '06', 100.00, 'source1')
insert into @IncomeTable values(2008, '06', 80.00, 'source2')
insert into @IncomeTable values(2008, '06', 200.00, 'source1')
insert into @IncomeTable values(2008, '06', 100.00, 'source2')

select source1.yr, source1.mon, source1.source_income - source2.source_income
from 
(select yr, mon, src, SUM(income) as source_income
from @IncomeTable
where src = 'source1'
group by yr, mon, src) source1
inner join 
(select yr, mon, src, SUM(income) as source_income
from @IncomeTable
where src = 'source2'
group by yr, mon, src) source2 on 
source1.yr = source2.yr and source1.mon = source2.mon

-- CK