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?

Does this answer your question? [Aggregate function in SQL WHERE-Clause](https://stackoverflow.com/questions/6319183/aggregate-function-in-sql-where-clause) — kmoser, Oct 17 '20 at 01:59
in case anyone facing a similar issue for a non-aggregate function do check out: https://stackoverflow.com/questions/60726887/how-to-use-new-created-column-in-where-column-in-sql — Wasit Shafi, Mar 05 '23 at 18:42

score 5 · Accepted Answer · answered Oct 16 '20 at 22:01

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

1 Answers1