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.

Hi,

Don't use the aggregate functions in the group by:

GROUP BY
A.PROD_NUM, B.ITEM_LEVEL, B.PURCHS_DATE,
DECODE(C.ITEM,'H',1,0),
DECODE(C.ITEM,'C',1,0)

Regards,

Arian

Hi,

Thank you for the reply Arian.

But the reply u sent me is still not executing. I am getting the same
error. Do we also show Count(*)Total_Items in group by. If so how do
we show that? I've tried the following but none of them is executing:

GROUP BY
A.PROD_NUM, B.ITEM_LEVEL, B.PURCHS_DATE,
DECODE(C.ITEM,'H',1,0),
DECODE(C.ITEM,'C',1,0);

GROUP BY
A.PROD_NUM, B.ITEM_LEVEL, B.PURCHS_DATE,
DECODE(C.ITEM,'H',1,0),
DECODE(C.ITEM,'C',1,0),
COUNT(*)TOTAL_ITEMS;

GROUP BY
A.PROD_NUM, B.ITEM_LEVEL, B.PURCHS_DATE,
DECODE(C.ITEM,'H',1,0),
DECODE(C.ITEM,'C',1,0),
TOTAL_ITEMS;

Plz help.

Thank you.

TR.

"TR" <tristan_robinson (AT) cooltoad (DOT) com> wrote

: Hi,
:
: Thank you for the reply Arian.
:
: But the reply u sent me is still not executing. I am getting the same
: error. Do we also show Count(*)Total_Items in group by. If so how do
: we show that? I've tried the following but none of them is executing:
:
: GROUP BY
: A.PROD_NUM, B.ITEM_LEVEL, B.PURCHS_DATE,
: DECODE(C.ITEM,'H',1,0),
: DECODE(C.ITEM,'C',1,0);
:
: GROUP BY
: A.PROD_NUM, B.ITEM_LEVEL, B.PURCHS_DATE,
: DECODE(C.ITEM,'H',1,0),
: DECODE(C.ITEM,'C',1,0),
: COUNT(*)TOTAL_ITEMS;
:
: GROUP BY
: A.PROD_NUM, B.ITEM_LEVEL, B.PURCHS_DATE,
: DECODE(C.ITEM,'H',1,0),
: DECODE(C.ITEM,'C',1,0),
: TOTAL_ITEMS;
:
: Plz help.
:
: Thank you.
:
: TR.
:

it would be helpful if you posted the entire statement along with the error
message, preferably from SQL*Plus with it's error location indication

++ mcs

On 20 Mar 2006 07:55:20 -0800, "TR" <tristan_robinson (AT) cooltoad (DOT) com>
wrote:


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