oracle SQL 上的条件求和

Conditional SUM on oracle SQL

提问by Imran Hemani

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).

我想要正数量的总和（数量 > 0）和负数量的总和（数量 < 0）。

How do I get those column sum based on condition?

如何根据条件获得这些列总和？

回答by Lukasz Szozda

You can use SUM(CASE ... ):

您可以使用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

LiveDemo

回答by MT0

You can use GREATESTand LEASTin conjunction with the SUMfunction:

您可以使用GREATEST和LEAST与该SUM功能结合使用：

SQL Fiddle

SQL小提琴

Oracle 11g R2 Schema Setup:

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

Query 1:

查询 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 |