valter.vx - 6 months ago 39

SQL 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 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

Answer

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.