sql - How to calculate between different group of rows of the same table

Question

Having a table as:

Year   Month   Value
2011   1       500
2011   2       550
2011   3       600
...
...
2012   1       600
2012   2       750
2012   3       930

Is there a way I can calculate the difference between values of same month/different years, so to get a result as:

Month    Value
1        100
2        200
3        330
...

I tried to do something like:

select month, a.value-b.value
from
  (select month, value from table where year = 2012) a,
  (select month, value from table where year = 2011) b

but the output is 12 months (of select a (2012) * 12 month of select b (2011)..

---

edit: sorry for the lack of important information:

The queries are being made to a excel sheet via odbc:jdbc bridge.

as the "from" clause is always like this: [sheet1$] i'm not being able to create any joins or case :(

Answer 1

You were very close to how to get the result. I would suggest using something similar to this which pivots the data into columns and then you can take the difference between each year:

select month,
  Year2012-Year2011 as Diff
from
(
  select month,
    sum(case when year = 2011 then value end) Year2011,
    sum(case when year = 2012 then value end) Year2012
  from yourtable
  group by month
) src     

See SQL Fiddle with Demo