467,915 Members | 1,270 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,915 developers. It's quick & easy.

urgent- difference sum within a colomn based on other colomn

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
  • viewed: 1005
Share:
1 Reply
ck9663
Expert 2GB
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.

Similar topics

9 posts views Thread by Stefan Bauer | last post: by
28 posts views Thread by Tamir Khason | last post: by
7 posts views Thread by zeyais | last post: by
33 posts views Thread by dembla | last post: by
1 post views Thread by alok sengar | last post: by
17 posts views Thread by Saps | last post: by
3 posts views Thread by N. Spiker | last post: by
1 post views Thread by rajesh.us.it.recruiter | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.