make the ELSE of one CASE WHEN equal to another CASE in the same SELECT clause

Question

How do I make the ELSE of one CASE WHEN equal to another CASE in the same SELECT clause?

In this example, the ELSE 0 under Cost equal to the Revenue. If the Cost is 0 use the Revenue value from above.

SELECT
    subselect.item,
    SUM(subselect.Revenue)
    SUM(subselect.Cost)
FROM(
    Select
        item,
        item_price,
        quantity,
        Item_cost,
        SUM(CASE  
            WHEN (item in ('1','2','3'))
            THEN item_price * quantity
            ELSE 0
            END) AS REVENUE,
        SUM(CASE  
            WHEN (item in ('1','2','3'))
            THEN item_cost * quantity
            ELSE 0
            END) AS COST
    ) AS subselect
...

Revenue:

item	item price	quantity	Revenue
1	1	2	2
2	3	1	6
3	2	3	6

Cost:

item	item cost	quantity	Cost
3	2	0	0

Since the Cost is 0, use the Revenue for the item, in this case 6.

I am using PL SQL Developer against an Oracle database.

Answer 1

From your data provided it looks like you don't need subselect at all since you could check the total cost with Case expression and choose what to select - COST or REVENUE.

WITH    -- S a m p l e   D a t a :
    revenue (ITEM, ITEM_PRICE, QUANTITY, REVENUE)  AS
        ( Select 1, 1, 2, 2 From Dual UNION All
          Select 2, 3, 1, 6 From Dual UNION All
          Select 3, 2, 3, 6 From Dual ),
    cost (ITEM, ITEM_PRICE, QUANTITY, ITEM_COST)  AS
        ( Select 3, 2, 0, 0 From Dual )

--     M a i n   S Q L :
Select      r.ITEM,
            SUM(r.ITEM_PRICE * r.QUANTITY) AS REVENUE,
            Case When  SUM(c.ITEM_COST * c.QUANTITY) = 0
                 Then  SUM(r.ITEM_PRICE * r.QUANTITY)
            Else SUM(c.ITEM_COST * c.QUANTITY)
            End AS COST
From        revenue r 
Inner Join  cost c ON(c.ITEM = r.ITEM)
Group By    r.ITEM

/*  R e s u l t :
      ITEM    REVENUE       COST
---------- ---------- ----------
         3          6          6    */