Group By - SQL Oracle Error: Invalid Item

Question

Trying to do a Group By ITEM but I can't seem to get it to work, ORA-00904: "ITEM": invalid identifier. This is what I have tried so far:

SELECT sum(quantity) AS SUPPLY, TRIM((SELECT ITEM_ID FROM INVENTORY_ITEM WHERE INVENTORY_ITEM_KEY = INVENTORY_SUPPLY.INVENTORY_ITEM_KEY)) AS ITEM FROM INVENTORY_SUPPLY
WHERE SUPPLY_TYPE = 'ONHAND' AND SHIPNODE_KEY IN ('LDC', 'LDC-WEB', 'LDC-SOS') GROUP BY ITEM;

There can be multiple entries in the INVENTORY_SUPPLY table for each ITEM (matched by INVENTORY_ITEM_KEY) and I need to sum these quantities into one row.

Please provide sample data, desired results, and an explanation of what you are trying to do. A non-working query does not (necessarily) provide this information. — Gordon Linoff
– Gordon Linoff, Commented Aug 11, 2021 at 17:39
The INVENTORY_SUPPLY table contains entries for each INVENTORY_ITEM_KEY with an on hand quantity for various SHIPNODE_KEYS. I am filtering only the SHIPNODE_KEYS I want in the query above, but this leaves 3 entries for each INVENTORY_KEY since they have different SHIPNODE_KEYS. I am using the INVENTORY_KEY to get the corresponding ITEM from a different table as that is more human friendly for reading. But without being able to SUM the QUANTITY by ITEM, I see 3 entries in the table for each ITEM and I need to SUM these into 1 entry. Does that help — AWooster
– AWooster, Commented Aug 11, 2021 at 17:43
I believe @GordonLinoff was trying to get you to do this: stackoverflow.com/help/minimal-reproducible-example — EdStevens
– EdStevens, Commented Aug 11, 2021 at 18:14

Littlefoot · Accepted Answer · 2021-08-11 19:59:11Z

2

According to my understanding, probably you will have to inner join the 2 tables instead of a scalar sub query, as group by executes first before the select clause. Hence it is not able to identify ITEM.

select sum(quantity) AS SUPPLY,  
ITEM_ID as ITEM
FROM INVENTORY_SUPPLY join INVENTORY_ITEM
on INVENTORY_ITEM.INVENTORY_ITEM_KEY = INVENTORY_SUPPLY.INVENTORY_ITEM_KEY
WHERE SUPPLY_TYPE = 'ONHAND' 
AND SHIPNODE_KEY IN ('LDC', 'LDC-WEB', 'LDC-SOS') 
GROUP BY INVENTORY_ITEM.ITEM_ID;

edited Aug 11, 2021 at 19:59

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

answered Aug 11, 2021 at 17:49

Sunil Kumar

913 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AWooster Over a year ago

That worked perfectly, sorry for not maybe explaining my question well enough, but you provided something that works for me!

Littlefoot Over a year ago

Why wouldn't you accept the answer, then, @AWooster?

Collectives™ on Stack Overflow

Group By - SQL Oracle Error: Invalid Item

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related