1,105,290 Community Members

#1111 - Invalid use of group function

Member Avatar
mogaka
Junior Poster in Training
58 posts since Sep 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 
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.

Member Avatar
pixelsoul
Posting Shark
918 posts since Feb 2011
Reputation Points: 174 [?]
Q&As Helped to Solve: 147 [?]
Skill Endorsements: 25 [?]
 
1
 

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
Member Avatar
pritaeas
mod_pritaeas
11,285 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,831 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
0
 

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

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