CODEWITHSUNDEEP
sqloracle11g
Touhidur
Touhidur

Reputation: 215

query not work in oracle

SELECT product_id,sum(quantity) AS total,price
FROM item,product
WHERE product.id=item.product_id
GROUP BY product_id

This query perfectly run in mysql but not work in oracle 11g XE. it's says:

ORA-00979: not a GROUP BY expression

Upvotes: 0

Views: 56

Answers (2)

Joshua Huber
Joshua Huber

Reputation: 3533

Like others have answered, Oracle requires you to explicitly GROUP BY any non-aggregated fields you SELECT. And your DBA will be happiest if you use an ANSI join syntax:

SELECT product_id, sum(quantity) AS total, price
  FROM item i
  JOIN product p ON (i.product_id = p.id)
 GROUP BY product_id, price;

Upvotes: 0

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

I guess you either want

SELECT product_id, sum(quantity) AS total, sum(price) as total_price
FROM item, product
WHERE product.id = item.product_id
GROUP BY product_id;

or

SELECT product_id, sum(quantity) AS total, price
FROM item, product
WHERE product.id = item.product_id
GROUP BY product_id, price;

Upvotes: 1

Related Questions