MYSQL - Unknown column in 'WHERE clause' using AS

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 

Replay

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”.

Dunno 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 ...

It 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.

Category: mysql Time: 2008-10-14 Views: 9
Tags: mysql

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.233 (s). 12 q(s)