difference of 2 records in a table per group

[frederik]

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?

 

[CJ_London]

bring your table through twice into a query, join them on year and district, then filter the first one on type=A and the second type=B. Something like

SELECT t1.Year, t1.district, "C" as CType, t1.value-t2.value as CValue
FROM myTable t1 INNER JOIN myTable t2 on t1.year=t2.year and t1.district=t2.district

Note year, type and value are all reserved words, using them as field names can cause errors, often with misleading error descriptions

 

[frederik]

Thank you CJ
I've tried the code, but I think there something missing and I can't see what.
I would expect less records than the orginal table, and it isn't the case.

Also, I suppose I have to add a where clause somewhere with type = A and type = B or something like that to exclude other possibilities (for example if type = F)

I've attached my test-file. Should you have a little moment, I would appreciate it a lot if you could have an eye on it.

 

[CJ_London]

copy this code, it should work

SELECT t1.jaar, t1.district, "C" AS soort, [t1].[waarde]-[t2].[waarde] AS diff
FROM Table1 AS t1 INNER JOIN Table1 AS t2 ON (t1.jaar = t2.jaar) AND (t1.district = t2.district)
WHERE (((t1.soort)="A") AND ((t2.soort)="B"));

 

[frederik]

Thank you, when I see it like this, it is a quiet simple solution.