By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

urgent- difference sum within a colomn based on other colomn

P: 4
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????
Oct 13 '08 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
Try this:


Expand|Select|Wrap|Line Numbers
  1. declare @IncomeTable table (yr int, mon varchar(2), income money, src varchar(10))
  2.  
  3. insert into @IncomeTable values(2008, '06', 100.00, 'source1')
  4. insert into @IncomeTable values(2008, '06', 80.00, 'source2')
  5. insert into @IncomeTable values(2008, '06', 200.00, 'source1')
  6. insert into @IncomeTable values(2008, '06', 100.00, 'source2')
  7.  
  8. select source1.yr, source1.mon, source1.source_income - source2.source_income
  9. from 
  10. (select yr, mon, src, SUM(income) as source_income
  11. from @IncomeTable
  12. where src = 'source1'
  13. group by yr, mon, src) source1
  14. inner join 
  15. (select yr, mon, src, SUM(income) as source_income
  16. from @IncomeTable
  17. where src = 'source2'
  18. group by yr, mon, src) source2 on 
  19. source1.yr = source2.yr and source1.mon = source2.mon

-- CK
Oct 13 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.