Danimov82 Danimov82 - 1 month ago 7
SQL Question

Complex Case Statement Issue - Oracle SQL

Wrote the query below, but am getting multiplied amounts because the aggregation needs to occur before the case statements. Would love some advice on the best way to structure this.

Select Store, CUSTID, CUST.ID_CUST,

Sum(
CASE
WHEN Cust_Gift.Code_Status = 'C' AND Gift_Item.FLAG_STORE_LOC = 'N'
THEN Cust_Gift.AMT_PAID ELSE 0
END) GiftAmt,

Sum(
CASE WHEN Cust_Gift.Code_Status = 'C' AND Gift_Item.FLAG_STORE_LOC = 'Y'
THEN Cust_Gift.AMT ELSE 0
END) CustGiftAmt,

Sum(
CASE WHEN Cust_Coupon.Code_Status = 'C'
THEN Cust_Coupon.AMT
ELSE 0
END) CouponAmt,

Sum(CASE WHEN Cust_Sports.Status = 'C'
THEN Cust_Sports.AMT
ELSE 0
END) SportsAmt

FROM CUST
LEFT OUTER JOIN CUST_GIFT
ON CUST.ID_CUST = CUST_GIFT.ID_CUST
LEFT OUTER JOIN CUST_COUPON
ON CUST.ID_CUST = CUST_COUPON.ID_CUST
LEFT OUTER JOIN CUST_SPORTS
ON CUST.ID_CUST = CUST_SPORTS.ID_CUST
INNER JOIN GIFT_ITEM
ON CUST_GIFT.ID_GIFT_ITEM = GIFT_ITEM.ID_GIFT_ITEM

WHERE (STORE = 'M669098' OR STORE = 'M66923434' )

Group by CustID, Store, CUST.ID_CUST

Answer

This is one way you could do it:

SELECT cust.store,
       cust.custid,
       cust.id_cust,
       gift.giftamt,
       gift.custgift,
       cpn.couponamt,
       sprt.sportsamt
FROM   cust
       LEFT OUTER JOIN (SELECT id_cust,
                               SUM(CASE WHEN cg.code_status = 'C' AND gi.flag_store_loc = 'N' THEN cg.amt_paid END) giftamt,
                               SUM(CASE WHEN cg.code_status = 'C' AND gi.flag_store_loc = 'Y' THEN cg.amt_paid END) custgiftamt
                        FROM   cust_gift cg
                               INNER JOIN gift_item gi ON cg.id_gift_item = gi.id_gift_item) gift ON cust.id_cust = gift.id_cust
       LEFT OUTER JOIN (SELECT id_cust,
                               SUM(CASE WHEN code_status = 'C' THEN amt END) couponamt
                        FROM   cust_coupon) cpn ON cust.id_cust = cpn.id_cust
       LEFT OUTER JOIN (SELECT id_cust,
                               SUM(CASE WHEN status = 'C' THEN amt END) sportsamt
                        FROM   cust_sports) sprt ON cust.id_cust = sprt.id_cust
WHERE  (STORE = 'M669098' OR STORE = 'M66923434');
Comments