Subtraction of values based on IDs and group after

Question

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 need 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 should be:

Year   Value
2011   -650  
2012   -1080 
...    ...

I'm just not managing to get this query working..

score 8 · Accepted Answer · answered Mar 06 '15 at 15:43

8

Use conditional aggregation:

select sum(case when type = 1 then value else - value end) as value
from table t
group by year;

answered Mar 06 '15 at 15:43

Gordon Linoff

Much better solution (+1)! – sgeddes Mar 06 '15 at 15:44
@sgeddes . . . Your solution is quite reasonable . . . although it could be improved by having an `end` so it is syntactically correct. ;) – Gordon Linoff Mar 06 '15 at 16:04

score 0 · Answer 2 · answered Mar 06 '15 at 15:45

0

You mean GROUP BY, CASE to decide sign +for type 1, - for others:

select "year", 
       SUM(case when type = 1 then value else -value end)
from yourtable
group by "year"

answered Mar 06 '15 at 15:45

jarlh

Oops. I'm a bit slow here... (Didn't notice Gordon Linoff's answer until now.) – jarlh Mar 06 '15 at 15:46

2 Answers2