Oracle SQL의 조건부 SUM

Oracle SQL의 조건부 SUM

저는 다음과 같은 방법으로 데이터를 가지고 있습니다.

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

양의 합(수량 > 0)과 음의 합(수량 < 0)을 원합니다.

조건에 따라 그 컬럼 합계를 어떻게 구합니까?

사용가능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

사용가능GREATEST그리고.LEAST와 함께SUM함수:

SQL Fiddle

Oracle 11g R2 스키마 설정:

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

쿼리 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

결과:

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

언급URL : https://stackoverflow.com/questions/33234844/conditional-sum-on-oracle-sql