Oracle Conditional sum

Question

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?

Answer 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

• Colin 't Hart: 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.
• User1974: @colin'thart This page has a handy table about what dbs support filter functionality: filter — Selective Aggregates.