mySQL using where on newly created column

Question

So, I think this is fairly simple, but I am messing something up.

I have the query

SELECT AVG(price), food_type
FROM instructor
GROUP BY food_type

Which produces this:

Price | food_type |
2.25 | Drink     |
1.50 | Candy     |
3    | Soup      |

And I am trying to return all prices over 2$

So I want:

Price | food_type |
2.25 | Drink     |
3    | Soup      |

It seems like it should be as simple as

SELECT AVG(price), food_type
FROM instructor
WHERE AVG(price) > 2
GROUP BY food_type

But I can't get this to work.

What am I doing wrong?

Answer 1

You can't use an aggregate function in the where clause. Instead, you need having:

SELECT AVG(price), food_type
FROM instructor
GROUP BY food_type
HAVING AVG(price) > 2

In MySQL, you can also refer to an alias in the HAVING clause (this is an extension to standard SQL):

SELECT AVG(price) avg_price, food_type
FROM instructor
GROUP BY food_type
HAVING avg_price > 2