Conditional SUM on oracle SQL

Question

I have the data in the follow way:

ITEM  LOCATION  UNIT RETAIL  QUANTITY 
100     KS         10          -10
200     KS         20           30

I want the sum of positive quantities (quantity > 0) and sum of negative quantities (quantity < 0).

How do I get those column sum based on condition?

Lukasz Szozda · Accepted Answer · 2015-10-20 11:09:10Z

27

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_sum
FROM your_table
GROUP BY item, location;

LiveDemo

edited Oct 20, 2015 at 11:09

answered Oct 20, 2015 at 11:00

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Comments

MT0 · Accepted Answer · 2015-10-20 11:18:16Z

4

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 DUAL
UNION ALL SELECT 100, 'KS', -10 FROM DUAL
UNION ALL SELECT 100, 'KS', -20 FROM DUAL
UNION ALL SELECT 100, 'KS',  10 FROM DUAL
UNION ALL SELECT 100, 'KS',   5 FROM DUAL
UNION ALL SELECT 200, 'KS',  10 FROM DUAL
UNION ALL SELECT 200, 'KS',  20 FROM DUAL
UNION 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_quantities
FROM   table_name
GROUP BY item, location

Results:

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

edited Oct 20, 2015 at 11:18

answered Oct 20, 2015 at 11:04

MT0

173k12 gold badges70 silver badges136 bronze badges

2 Comments

Lukasz Szozda Over a year ago

I like the idea of hiding CASE with GREATEST/LEAST :) Could you compare execution plans with my solution?

MT0 Over a year ago

@lad2025 Unsurprisingly - they both have identical plans. If you wish to profile them you can see if they have different performance (but I doubt it as they do exactly the same thing). Ultimately it comes down to which is more readable/maintainable/easier to type/etc...

Collectives™ on Stack Overflow

Conditional SUM on oracle SQL

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related