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

Question

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 ;)

Answer 1

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

Answer 2

If you're really just looking for the "1 ad with the most photos", you might want something like:

select i.t_1,count(*) n ... group by i.t_1 order by n desc limit 1