0

This question was born in my mind looking at this kind of code:

select sum(value)
  into positive
  from my_table
 where value > 0;

 select sum(value)
  into negative
  from my_table
 where value < 0;

This works well for take the totals of this table.

But, my question is how I can get the sub-totals of my_table using only a statement.

expected result:

Having this data on my_table

VALUE
-----
  1.0
 -2.0
  3.0
 -4.0
  5.0
 -6.7
  8.9

I'd a DML statement who can give me this output

positive | negative
---------+---------
    17.9 |    -12.7

Is this kind of operation possible? If yes, how can i get it?

1
SQL> r
  1  with t as (
  2      select  1.0 as x from dual
  3      union all select -2.0 as x from dual
  4      union all select  3.0 as x from dual
  5      union all select -4.0 as x from dual
  6      union all select  5.0 as x from dual
  7      union all select -6.7 as x from dual
  8      union all select  8.9 as x from dual)
  9  select sum(case when x > 0 then x else 0 end) as sum_of_positives,
 10         sum(case when x < 0 then x else 0 end) as sum_of_negatives
 11*   from t

SUM_OF_POSITIVES SUM_OF_NEGATIVES
---------------- ----------------
            17,9            -12,7
  • Hopefully Oracle one day supports the filter syntax: select count(*) filter (where x > 0) as positive, count(*) filter (where x < 0) as negative from t; This works in PostgreSQL 9.4+, if anyone is interested. – Colin 't Hart Apr 16 '15 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.