MySQL where clause and ordering by avg() as a sub query

mysql subquery where average clause
Mike picture Mike · Jul 30, 2009 · Viewed 7.3k times · Source

Although I can group and order by on an aliased sub query, I can't use the alias in a where clause. Do I need to use a join instead?

Works:

SELECT entries.*, 
    (SELECT avg(value) 
    FROM `ratings`
    WHERE ratings.entry_id = entries.id) as avg_rating
FROM `entries` 
ORDER BY avg_rating DESC

Fails ("unknown column 'avg_rating' in where clause"):

SELECT entries.*, 
    (SELECT avg(value) 
    FROM `ratings` 
    WHERE ratings.entry_id = entries.id) as avg_rating 
FROM `entries` 
WHERE avg_rating < '4.5000' ORDER BY avg_rating DESC

Answer

Jason picture Jason · Jul 30, 2009

You may be able to do this with a HAVING clause instead of a WHERE

Syntax

Related questions

MySQL Error 1093 - Can't specify target table for update in FROM clause

I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries: SELECT * FROM story_category WHERE category_id NOT IN ( SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_…

Read More
Join vs. sub-query

I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query, and I hate it; I don't know why. I lack the theoretical knowledge to judge for myself if there is any difference. …

Read More
What is the error "Every derived table must have its own alias" in MySQL?

I am running this query on MySQL SELECT ID FROM ( SELECT ID, msisdn FROM ( SELECT * FROM TT2 ) ); and it is giving this error: Every derived table must have its own alias. What's causing this error?

Read More