How to calculate value with code for specific formule in Oracle SQL?

Question

I have Oracle SQL query. In results I get something like that:

CODE || VALUE
I || 10
II || 30
III || 50
IV || -20
V || 60
VII || -45
VIII || 0
IX || 100
X || 0

Next I want for CODE = X create formule like:

CASE 1:

SUM OF I,II,III,IV,V,IX

CASE 2:

I+II+III-IV-V+IX

How can I implent these formule in sql query?

Gordon Linoff · Accepted Answer · 2018-12-09 18:01:11Z

1

You would use aggregation:

select sum(case when code in ('I', 'II', 'III', 'IV', 'V', 'IX') then value else 0 end) as case1,
       sum(case when code in ('I', 'II', 'III', 'IX') then value
                when code in ('IV', 'V') then -value
                else 0
           end) as case2
from t;

answered Dec 9, 2018 at 18:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Himanshu · Accepted Answer · 2018-12-09 18:17:14Z

0

      select sum(value) from table where code not like 'VI%'   
      Union
      select sum(case when code in ('IV', 'V' ) then -value
      else value end case) from table where code not like 
        'VI%'

Just a different way of doing the same as above via union

answered Dec 9, 2018 at 18:17

Himanshu

3,9802 gold badges16 silver badges34 bronze badges

Collectives™ on Stack Overflow

How to calculate value with code for specific formule in Oracle SQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related