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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.