126

I need to use an alias in the WHERE clause, but It keeps telling me that its an unknown column. Is there any way to get around this issue? I need to select records that have a rating higher than x. Rating is calculated as the following alias:

sum(reviews.rev_rating)/count(reviews.rev_id) as avg_rating
Improve this question

5 Answers 5

Reset to default
238

You could use a HAVING clause, which can see the aliases, e.g.

 HAVING avg_rating>5

but in a where clause you'll need to repeat your expression, e.g. 

 WHERE (sum(reviews.rev_rating)/count(reviews.rev_id))>5

BUT! Not all expressions will be allowed - using an aggregating function like SUM will not work, in which case you'll need to use a HAVING clause.

From the MySQL Manual:

It is not allowable to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section B.1.5.4, “Problems with Column Aliases”.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

If I repeat the expression, it tells me: "invalid use of group function"
Have reworded to make it clearer aggregrating functions not allowedd
Nice explanation, esp. the "but in a where clause... repeat.." part
This is a fine answer, but please consider the performance implications since HAVING executes after the data is fetched and WHERE executes before.
You can't use aggregate functions in a WHERE clause. The WHERE clause only filters rows one at a time, not whole groups.
33

I don't know if this works in mysql, but using sqlserver you can also just wrap it like:

select * from ( -- your original query select .. sum(reviews.rev_rating)/count(reviews.rev_id) as avg_rating from ...) Foo where Foo.avg_rating ...
Improve this answer

Comments

8

This question is quite old and one answer already gained 160 votes...

Still I would make this clear: The question is actually not about whether alias names can be used in the WHERE clause.

sum(reviews.rev_rating) / count(reviews.rev_id) as avg_rating

is an aggregation. In the WHERE clause we restrict records we want from the tables by looking at their values. sum(reviews.rev_rating) and count(reviews.rev_id), however, are not values we find in a record; they are values we only get after aggregating the records.

So WHERE is inappropriate. We need HAVING, as we want to restrict result rows after aggregation. It can't be

WHERE avg_rating > 10

nor

WHERE sum(reviews.rev_rating) / count(reviews.rev_id) > 10

hence.

HAVING sum(reviews.rev_rating) / count(reviews.rev_id) > 10

on the other hand is possible and complies with the SQL standard. Whereas

HAVING avg_rating > 10

is only possible in MySQL. It is not valid SQL according to the standard, as the SELECT clause is supposed to get executed after HAVING. From the MySQL docs:

Another MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list.

The MySQL extension permits the use of an alias in the HAVING clause for the aggregated column

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Improve this answer

Comments

1 
SELECT * FROM (SELECT customer_Id AS 'custId', gender, age FROM customer WHERE gender = 'F') AS c WHERE c.custId = 100;
Improve this answer

Comments

0

If your query is static, you can define it as a view then you can use that alias in the where clause while querying the view.

Improve this answer

Comments