Search code examples
mysqlmysql-error-1111

Mysql how to use COUNT value of rows from joined table in WHERE statement?

Like that:

COUNT(i.t_1) AS total_images
WHERE total_images > 2

Throws an error:

Unknown column "total_images" in where clause

If this way:

WHERE COUNT(i.t_1) > 2

Throws an error:

Invalid use of group function

How to do it right way?

If need i'll post full statement.

The meaning of this query to pick the 1 ad with the most photos inside joined(images) table.

Thanks ;)

Solution

  • The WHERE clause can only be used to filter rows in the table / derived table on a row-by-row basis. To filter based on the results of an aggregation you need to using HAVING instead of WHERE:

    HAVING COUNT(i.t_1) > 2