1

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

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Somebody
  • 9,316
  • 26
  • 94
  • 142

2 Answers2

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
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Thanks, it's working, i'll have to read man about HAVING ;) will approve after 10 min – Somebody Oct 23 '10 at 11:02
  • @Beck: Check out this related question: http://stackoverflow.com/questions/287474/sql-whats-the-difference-between-having-and-where - *SQL: What's the difference between HAVING and WHERE?* – Mark Byers Oct 23 '10 at 11:05
0

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

igelkott
  • 1,287
  • 8
  • 9