ORA-00937: not a single-group group function with Aggregation in CASE statement

Question

I have the below query (being used as a Subquery in an outer query) that is erroring with ORA-00937: not a single-group group function. Depending on if ITM2.UNIT_OF_ISSUE is NULL or not I need to do the calculation different as you see below.

SELECT CASE WHEN ITM2.UNIT_OF_ISSUE IS NOT NULL 
            THEN 
             ( SUM(IOQD.PRIMARY_TRANSACTION_QUANTITY) /  UOM2.CONVERSION_RATE)

            ELSE SUM(ITM2.PRIMARY_UOM_CODE) 
       END AS ONHAND_QTY

FROM EGP_SYSTEM_ITEMS ITM2, INV_ONHAND_QUANTITIES_DETAIL IOQD, inv_uom_conversions UOM2

WHERE IOQD.INVENTORY_ITEM_ID = ITM2.INVENTORY_ITEM_ID
 AND IOQD.ORGANIZATION_ID = ITM2.ORGANIZATION_ID 
 AND UOM2.INVENTORY_ITEM_ID = ITM2.INVENTORY_ITEM_ID
 AND UOM2.UOM_CODE = ITM2.UNIT_OF_ISSUE

I tried using Group By with the entire CASE expression above, however that resulted in error ORA-00934

How can I fix this?

have you tried adding a group by after the where clause? Please post [mcve] — OldProgrammer, Feb 28 '23 at 17:30
Yes I did, thats where the 2nd error resulted with grouping by the expression. — Nick, Feb 28 '23 at 17:37
https://stackoverflow.com/questions/11325268/how-do-i-use-group-by-based-on-a-case-statement-in-oracle — OldProgrammer, Feb 28 '23 at 17:41

score 0 · Answer 1 · answered Feb 28 '23 at 17:59

0

You need to group by the non-aggregated columns referenced in your case expression, not the whole case expression:

GROUP BY ITM2.UNIT_OF_ISSUE, UOM2.CONVERSION_RATE

fiddle (including one with ANSI joins...)

answered Feb 28 '23 at 17:59

Alex Poole

183,384
11
179
318

ORA-00937: not a single-group group function with Aggregation in CASE statement

1 Answers1