Can you use an alias in the WHERE clause in mysql?
MysqlSqlHavingHaving ClauseMysql Problem Overview
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
Mysql Solutions
Solution 1 - Mysql
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”.
Solution 2 - Mysql
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 ...
Solution 3 - Mysql
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
Solution 4 - Mysql
SELECT * FROM (SELECT customer_Id AS 'custId', gender, age FROM customer
WHERE gender = 'F') AS c
WHERE c.custId = 100;
Solution 5 - Mysql
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.