Microsoft DN Microsoft DN - 1 month ago 9
SQL Question

"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;

Answer

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
Comments