CODEWITHSUNDEEP
sqloracle10ggroup-by
Microsoft DN
Microsoft DN

Reputation: 10020

"Group By" not working properly

I have a table called ‘ORDER_DETAILS’

enter image description here

And a table called PRODUCT_DETAIL enter image description here

I want to fetch data like

enter image description here

Order_total would be sum of (Quantity*price) -> (2*10)+(2*100)+(4*20)=300

I used following query

Select  Order_id, (ROUND(SUM(ql.price * ql.quantity), 2) Order_total
From ORDER_DETAILS o
Inner join PRODUCT_DETAIL p
On o.order_id=p.order_id
Group by Order_id

But it is giving error ORA-00979: not a GROUP BY expression

what I am doing wrong here. I know it would be pretty simple but cannot figure out the problem.

EDIT:

Edited query

select o.order_id, round(sum(p.price * p.quantity),2) order_total
from order_details o
inner join product_detail p
on o.order_id = p.order_id
group by o.order_id;

Upvotes: 0

Views: 7543

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

Even with the replaced table names as @a_horse_with_no_name recommends, you have a number of other problems, though oddly none of them will lead to a ORA-00979. Might as well list them, though this isn't intended to be harsh...

  • you're using table alias ql but that isn't defined;
  • you're referring to column unit_price when you're said the column is just price;
  • you're missing a closing ), or more sensible you have an extra ( before ROUND; (I'm not sure ROUND is useful - it won't do much unless your prices are fractions of pence/cents/whatever);
  • you have a column called order_id in both tables but you aren't specifying which to use in the select or the order by.

With your new table names this works:

select o.order_id, round(sum(p.price * p.quantity),2) order_total
from order_details o
inner join product_detail p
on o.order_id = p.order_id
group by o.order_id;

  ORDER_ID ORDER_TOTAL
---------- -----------
         1         300

If the ROUND was intended to make it show two decimal places, it won't; you'd need to use TO_CHAR instead, maybe:

select o.order_id, to_char(sum(p.price * p.quantity), '999G999D99') order_total
...

  ORDER_ID ORDER_TOTAL
---------- -----------
         1      300.00

Upvotes: 1

Related Questions