Hi,
I have a table with following fields / data:
Year ; district ; type ; value
2000 ; 00001 ; A ; 10000
2000 ; 00001 ; B ; 5000
2000 ; 00002 ; A ; 20000
2000 ; 00002 ; B ; 10000
2001 ; 00001 ; A ; 15000
2001 ; 00001 ; B ; 10000
2001 ; 00002 ; A ; 25000
2001 ; 00002 ; B ; 20000
What I need is a query that makes for each group "Year / district" the difference between A and B and define that as C
So the result of the query would be:
Year ; district ; type ; value
2000 ; 00001 ; C ; 5000
2000 ; 00002 ; C ; 10000
2001 ; 00001 ; C ; 5000
2001 ; 00002 ; C ; 5000
I can do this by doing a transpose first of the original table which gives me a column A en B so I can easily calculate the difference per record.
I would like to do it without a transpose because that's a little cumbersome.
Instead, I would like to do it directly in SQL, however I can't directly see how to do it. Can someone please direct me in the right direction?
I have a table with following fields / data:
Year ; district ; type ; value
2000 ; 00001 ; A ; 10000
2000 ; 00001 ; B ; 5000
2000 ; 00002 ; A ; 20000
2000 ; 00002 ; B ; 10000
2001 ; 00001 ; A ; 15000
2001 ; 00001 ; B ; 10000
2001 ; 00002 ; A ; 25000
2001 ; 00002 ; B ; 20000
What I need is a query that makes for each group "Year / district" the difference between A and B and define that as C
So the result of the query would be:
Year ; district ; type ; value
2000 ; 00001 ; C ; 5000
2000 ; 00002 ; C ; 10000
2001 ; 00001 ; C ; 5000
2001 ; 00002 ; C ; 5000
I can do this by doing a transpose first of the original table which gives me a column A en B so I can easily calculate the difference per record.
I would like to do it without a transpose because that's a little cumbersome.
Instead, I would like to do it directly in SQL, however I can't directly see how to do it. Can someone please direct me in the right direction?