SQL with a condition for the calculated value

Question

I have a table with products, their quantity and their price. I need to select all entries where the average price per article is between the range. My request:

SELECT productid,AVG(SUM(price)/SUM(amount)) AS avg 
FROM stock WHERE avg>=$from AND avg<=$to GROUP BY productid

If so, this suggests that avg does not exist. In addition, I obviously need to group, because the amount and average should be for the wine

Answer 1

You need to put it in a sentence HAVING. You cannot filter the result of aggregates with WHERE.

MySQL allows you to reference column aliases in HAVING. You may need

SELECT 
      productid,
      AVG(price/amount) AS avg ,
      /*SUM(price)/SUM(amount) AS avg (<--- Or perhaps this?)*/
FROM stock 
GROUP BY productid
HAVING avg>=$from AND avg<=$to 

But I'm not sure what exactly you are trying to do with AVG(SUM(price)/SUM(amount)), can you show some example data?