#1111 - Invalid use of group function

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.

Featured Replies in this Discussion


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

I think you'll need a sub-select to calculate the new value.

This article has been dead for over six months: Start a new discussion instead
Start New Discussion
Tags Related to this Article