CODEWITHSUNDEEP
mysqlgroup-byconditional-statements
lawl0r
lawl0r

Reputation: 870

SQL with condition on calculated value

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

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

If do this, it tells me avg doesn't exist. Also I obviously need to group by because the sum and average need to be per wine

Upvotes: 3

Views: 696

Answers (1)

Martin Smith
Martin Smith

Reputation: 453608

You need to put it in the HAVING clause. You cannot filter by the result of aggregates using WHERE.

MySQL does allow you to reference column aliases in HAVING. You might 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 exactly what you are trying to do with AVG(SUM(price)/SUM(amount)) can you show some example data?

Upvotes: 3

Related Questions