Conditional SUM on oracle SQL

You can use SUM(CASE ... ):

SELECT item, location,   SUM(CASE WHEN quantity > 0 THEN quantity ELSE 0 END) AS positive_sum,   SUM(CASE WHEN quantity < 0 THEN quantity ELSE 0 END) AS negative_sumFROM your_tableGROUP BY item, location;

LiveDemo

You can use GREATEST and LEAST in conjunction with the SUM function:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( ITEM, LOCATION, QUANTITY ) AS          SELECT 100, 'KS', -10 FROM DUALUNION ALL SELECT 100, 'KS', -10 FROM DUALUNION ALL SELECT 100, 'KS', -20 FROM DUALUNION ALL SELECT 100, 'KS',  10 FROM DUALUNION ALL SELECT 100, 'KS',   5 FROM DUALUNION ALL SELECT 200, 'KS',  10 FROM DUALUNION ALL SELECT 200, 'KS',  20 FROM DUALUNION ALL SELECT 200, 'KS',   5 FROM DUAL

Query 1:

SELECT item,       location,       SUM( GREATEST( quantity, 0 ) ) AS positive_quantities,       SUM( LEAST( quantity, 0 ) )    AS negative_quantitiesFROM   table_nameGROUP BY item, location

Results:

| ITEM | LOCATION | POSITIVE_QUANTITIES | NEGATIVE_QUANTITIES ||------|----------|---------------------|---------------------||  100 |       KS |                  15 |                 -40 ||  200 |       KS |                  35 |                   0 |