I'm using Oracle.
Having a table as:
Year Type Value
2011 1 500
2011 2 550
2011 3 600
...
...
2012 1 600
2012 2 750
2012 3 930
I needed to subtract all the values from different types, grouped by year. The operation would be:
For 2011 -> 1-2-3 (500-550-600)
For 2012 -> 1-2-3 (600-750-930)
For ...
The result shoud be:
Year Value
2011 -650
2012 -1080
... ...
I couldn't do it but here on stack overflow this query was suggested, and it worked:
select sum(case when type = 1 then value else - value end) as value
from table t
group by year;
But now, i have another situation. I need to do the same thing but not 1-2-3-4-5-... But 1+2-3-4-5-6....
To to this i tried this both queries, with no sucess:
select sum(case when type = 1 then value when type = 2 then value else - value end) as value
from table t
group by year;
This resulted in the second value has its value doubled.
select sum(case when type = 1 then value else - value end)+case when type = 2 then value as value
from table t
group by year;
This resulted in a correct type 2 value, but,as this type 2 only accurs in some years, the other years show up as null. So, the final calculation is correct for type 2 (the years it is in) but for every other year, for each type 2 does not exist, it returns null.
I'm just not managing to get this query working.. Any idea would be greatly appreciated! Thanks
The determination of whether the number should be treated as positive or negative needs to happen inside of your aggregation SUM(). So:
select sum(case when type = 1 OR type = 2 then value else - value end)
from table t
group by year;
Because both 1 and 2 are positive in your 1+2-3-4-5-6... formula (the 1 being positive is implied), so you need the OR to make sure both are positive and everything else is negative. Then it will be summed and your golden.