using generated column in a MySQL where clause

Question

If I have a query, such as:

select column1, (
    select count(*) from table2
) as some_count
from table1
where column1 = 'foo'

I can use column1 in the where clause, but if I try to add

and some_count > 0

then I get an error that some_count doesn't exist. How can I use that in a where clause?

Answer 1

As is, the only way is to use a derived table:

SELECT x.*
  FROM (select column1, 
               (select count(*) from table2) as some_count
          from table1
         where column1 = 'foo') x
 WHERE x.some_count > 0

Do you realize that without correlation, some_count is going to return the same value for every row returned?

Answer 2

Use HAVING

select column1, (
    select count(*) from table2
) as some_count
from table1
HAVING some_count > 0