CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Cronas De Se
Cronas De Se

Reputation: 331

Sum a field only on specific conditions in Oracle SQL

I'm trying to get the amounts from a table where I want to sum some amounts that satisfies a specific condition while retaining the un-summed amount of others if it did not meet the condition.

I was trying this solution but it still doesn't sum up even if it meets the condition.

SELECT
  price_type,
  CASE trim(price_type)
    WHEN 'TYPE1' THEN sum(bill_amt)
    ELSE bill_amt
  END bill_amt
FROM (
  SELECT 
    price_type,
    bill_amt
  FROM price_val_tbl
)
GROUP BY price_type, bill_amt

Expected Output:

PRICE_TYPE   BILL_AMT
==========   ========
TYPE1        50
TYPE2        100
TYPE2        200

Actual Output

PRICE_TYPE   BILL_AMT
==========   ========
TYPE1        100
TYPE1        -50
TYPE2        100
TYPE2        200

Upvotes: 1

Views: 289

Answers (2)

Popeye
Popeye

Reputation: 35900

You can also use conditional GROUP BY as follows:

SQL> SELECT A, SUM(B)
  2  FROM YOUR_DATA
  3  GROUP BY A, ( CASE WHEN A = 'T1' THEN A ELSE TO_CHAR(ROWNUM) END);

A      SUM(B)
-- ----------
T2        100
T2        200
T1         50

SQL>

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This is a case where you want UNION ALL:

SELECT price_type, SUM(bill_amt)
FROM price_val_tbl
WHERE trim(price_type) = 'TYPE1'
GROUP BY price_type
UNION ALL
SELECT price_type, bill_amt
FROM price_val_tbl
WHERE trim(price_type) <> 'TYPE1';

The reason you need UNION ALL is because you have no unique identifier on each row. You could use a subquery and aggregation like this:

SELECT MIN(price_type) as price_type,
       SUM(bill_amt) as billL_amt
FROM (SELECT pv.*, rownum as rn
      FROM price_val_tbl pv
     ) pv
GROUP BY (CASE WHEN trim(price_type) = 'TYPE1' THEN -1 ELSE rn END) ;

However, this is less clear on what it is doing.

Upvotes: 1

Related Questions