CODEWITHSUNDEEP
sqloracle-database
spyr0
spyr0

Reputation: 199

Oracle SQL: Group by not working with aggregates in CASE statement

My SQL query is currently:

SELECT ARINVT.CLASS,
 ARINVT.ITEMNO,
 ARINVT.DESCRIP,
 ARINVT.ONHAND,
 CASE 
 WHEN DAY_USE_TOTAL.CUM_MAT_QTY IS NULL THEN 0
 ELSE SUM (DAY_USE_TOTAL.TOT_MAT_QTY) END "Material Usage"

FROM IQMS.ARINVT ARINVT
 LEFT JOIN IQMS.DAY_USE_TOTAL DAY_USE_TOTAL
    ON ARINVT.ID = DAY_USE_TOTAL.ARINVT_ID


WHERE CLASS = 'PL'
GROUP BY ARINVT.ID,
     ARINVT.CLASS,
     ARINVT.ITEMNO,
     ARINVT.DESCRIP,
     ARINVT.ONHAND

ORDER BY ARINVT.ITEMNO

I'm currently getting the error ORA-00979: not a GROUP BY expression. When I remove the case statement and just have the SUM():

SELECT ARINVT.CLASS,
 ARINVT.ITEMNO,
 ARINVT.DESCRIP,
 ARINVT.ONHAND,
 SUM (DAY_USE_TOTAL.TOT_MAT_QTY) 

FROM IQMS.ARINVT ARINVT
 LEFT JOIN IQMS.DAY_USE_TOTAL DAY_USE_TOTAL
    ON ARINVT.ID = DAY_USE_TOTAL.ARINVT_ID


WHERE CLASS = 'PL'
GROUP BY ARINVT.ID,
     ARINVT.CLASS,
     ARINVT.ITEMNO,
     ARINVT.DESCRIP,
     ARINVT.ONHAND

ORDER BY ARINVT.ITEMNO

It works perfectly fine. Why is this? I think it would be because the CASE statement can return 0 which doesn't work with the GROUP BY, but even having both returning as aggregates doesn't seem to fix this. How can I resolve this? I'm try to use a subquery at the moment but at the moment not much success with it.

Upvotes: 2

Views: 145

Answers (2)

Trung Duong
Trung Duong

Reputation: 3475

You could try 

SELECT ARINVT.CLASS,
 ARINVT.ITEMNO,
 ARINVT.DESCRIP,
 ARINVT.ONHAND,
 SUM (CASE WHEN DAY_USE_TOTAL.CUM_MAT_QTY IS NULL THEN 0
 ELSE DAY_USE_TOTAL.TOT_MAT_QTY END) "Material Usage"

FROM IQMS.ARINVT ARINVT
 LEFT JOIN IQMS.DAY_USE_TOTAL DAY_USE_TOTAL
    ON ARINVT.ID = DAY_USE_TOTAL.ARINVT_ID


WHERE CLASS = 'PL'
GROUP BY ARINVT.ID,
     ARINVT.CLASS,
     ARINVT.ITEMNO,
     ARINVT.DESCRIP,
     ARINVT.ONHAND

ORDER BY ARINVT.ITEMNO

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132570

Do this:

 SUM (CASE WHEN DAY_USE_TOTAL.CUM_MAT_QTY IS NULL THEN 0
 ELSE DAY_USE_TOTAL.TOT_MAT_QTY END) "Material Usage"

Upvotes: 4

Related Questions