SQL Problem

[Ericksco]

Hi there... I need help in finding out if this can be accomplished...

MY_TABLE

ItemCod Item_subcode Item_Category Amount
------- ------------ ------------- ------
ITM01 001 BOOK 50.00
ITM01 002 BOOK_A 20.00
ITM01 003 BOOK_A 30.00

DESIRED OUTPUT
==============

Item_Category Total Amount
------------- ------------
BOOK 100.00


I know that I can do this using several steps:

Step 1)

CREATE TABLE NEW_TABLE AS
SELECT (SELECT ITEM_CATEGORY
FROM MY_TABLE X
WHERE A.ITEM_COD = X.ITEM_COD AND
A.ITEM_COD = 001) HI_ITEM_CATEGORY,
AMOUNT

FROM MY_TABLE A

Step 2)

SELECT HI_ITEM_CATEGORY, SUM(AMOUNT)
FROM NEW_TABLE
GROUP BY HI_ITEM_CATEGORY

Can this be accomplished by a single SELECT statement?

Thanks in advance!

EOF

 

[rcurva]

Try this one. ===>

SELECT SUBSTR(Item_category,1,INSTR(Item_category,'_')-1) "Item_Category", SUM(Amount) "Total amount"
FROM MY_TABLE;

My assumption here is, all subscripts of the Item Category is delimited by an underscore "_" character.

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War