SQL Conditional aggregation

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

score 3 · Accepted Answer · answered Mar 06 '15 at 18:30

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.

Sylvain Leroux · Answer 2 · 2015-03-06T21:03:24.430

1

For sake of completeness, two alternate solutions:

select sum(case when type in (1, 2) then value else -value end)
from t
group by year;

Or, using a simple case expression:

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

edited Mar 06 '15 at 21:03

answered Mar 06 '15 at 19:03

Sylvain Leroux

50,096
7
103
125

Hi, thanks for the answer. Isn't the second query the same as my first try? It doubled the value.. As example. The value was 1000, that should be added to 500, resulting in 1500.. By doing that, it added 2000, being the total result 2500.. strange.. – valter.vx Mar 06 '15 at 19:20
@valter.vx This is a slight variation on the syntax you tried. As I said "for sake of completeness". However, I cannot explain the behavior you observed. All those queries (yours, mine, JNevill's one) should produce the exact same result. As a matter of fact they did here on my Oracle 11g XE test system. – Sylvain Leroux Mar 06 '15 at 21:05

score 0 · Answer 3 · answered Mar 06 '15 at 18:44

0

Short version with sign used:

select year, sum(sign(-type+2.5)*value) from table t group by year

answered Mar 06 '15 at 18:44

Ponder Stibbons

14,723
2
21
24

SQL Conditional aggregation

3 Answers3