Total Result, (Sum or Subtract some Value) according to Flag Column Oracle SQL Developer

Question

I'm newbie in SQL Developer.

I have a query result like the above image.

I want to know how to sum all values with Flag = 1 and to subtract all the values With Flag = 2 in order to obtain a total result?

Answer 1

With a little help of DECODE, here's how:

SQL> with test (value, flag) as
  2    (select 100, 2 from dual union  -- sum of flag 2 values = 600
  3     select 200, 2 from dual union
  4     select 300, 2 from dual union
  5     --
  6     select 700, 1 from dual union  -- sum of flag 1 values = 1500
  7     select 800, 1 from dual
  8    )
  9  select sum(decode(flag, 1, value, 0)) sum_1,
 10         sum(decode(flag, 2, value, 0)) sum_2,
 11         --
 12         sum(decode(flag, 1, value, 0)) - sum(decode(flag, 2, value, 0)) result
 13  from test;

     SUM_1      SUM_2     RESULT
---------- ---------- ----------
      1500        600        900

SQL>