Re: Error at Group By clause

On 20 Mar 2006 07:55:20 -0800, "TR" <tristan_robinson_at_cooltoad.com> wrote:

>Hi,
>I have the following sql query.
>
>SELECT
>A.PROD_NUM, B.ITEM_LEVEL, B.PURCHS_DATE,
>SUM(DECODE(C.ITEM,'H',1,0))HOT,
>SUM(DECODE(C.ITEM,'C',1,0))COLD,
>COUNT(*)TOTAL_ITEMS
>FROM PROD_DETAIL A, ITEM_DETAIL B, PURCHASE_DETAIL C
>WHERE
>A.PROD_ID = B.PROD_ID AND
>B.ITEM_ID = C.ITEM_ID
>GROUP BY
>A.PROD_NUM, B.ITEM_LEVEL, B.PURCHS_DATE,
>SUM(DECODE(C.ITEM,'H',1,0))HOT,
>SUM(DECODE(C.ITEM,'C',1,0))COLD,
>COUNT(*)TOTAL_ITEMS;
>
>I want the output as follows in 6 different columns:
>Prod_Num Item Level Purchase_Date Hot Cold
>Total_Items
>
>When I am executing the query I am getting the following error:
>GROUP_BY
>*
>ERROR at line 10:
>ORA-00933: SQL command not properly ended
>
>Its pointing the error at GROUP BY. Can any one of you plz explain me
>where I am going wrong and how to execute the command correctly?
>
>Thank you.
>
>TR.

SELECT
A.PROD_NUM, B.ITEM_LEVEL, B.PURCHS_DATE, SUM(DECODE(C.ITEM,'H',1,0)) HOT,
SUM(DECODE(C.ITEM,'C',1,0)) COLD,
COUNT(*) TOTAL_ITEMS
FROM PROD_DETAIL A, ITEM_DETAIL B, PURCHASE_DETAIL C WHERE
A.PROD_ID = B.PROD_ID AND
B.ITEM_ID = C.ITEM_ID
GROUP BY
A.PROD_NUM, B.ITEM_LEVEL, B.PURCHS_DATE;

--
Sybrand Bakker, Senior Oracle DBA