Start New Discussion within our Databases Community
update drugs,drug_sales SET drugs.b_price=(SUM(drug_sales.fee)/SUM(drug_sales.quantity)) WHERE drugs.drug_id=drug_sales.drug_id AND drug_sales.pid=0

update drugs,drug_sales SET drugs.b_price=SUM(drug_sales.fee)/SUM(drug_sales.quantity) in drugs.drug_id=drug_sales.drug_id AND drug_sales.pid=0

The intention of the above 2 queries is to update a table called drugs based on computational results of table called drug_sales. The two queries fails and the error is: "#1111 - Invalid use of group function".

Please help me solve/debug the query. am using mysql DB.

So you're saying that this doesn't work?

UPDATE drugs,drug_sales SET drugs.b_price=SUM(drug_sales.fee)/SUM(drug_sales.quantity) WHERE drugs.drug_id=drug_sales.drug_id AND drug_sales.pid=0
This article has been dead for over six months. Start a new discussion instead.