Pesky IF/THEN statement in mysql query

Question

> select * from site, count(*) as count  
> from myTable 
> where year(created_at) = 2012
> group by site order by count DESC limit 10000

I'm selecting a massive amount of data that essentially has a really long and useless tail.

I'm trying to cut off this query so it doesn't show anything with less than 500 results.

All of the Googling I've done for the solution to this hasn't proved very helpful.

Any idea how to construct the query to limit this data to only show sites with a count of 500 or more?

Answer 1

select 
  site , 
  count(*) as val 
from 
  myTable 
where 
  year(created_at) = 2012 
and 
  val > 500 
group by 
  site
HAVING COUNT(*) > 500

edit

Points go to Sean - I realized I was using MSSQL syntax, not MySQL

Also - refer to FIlter by COUNT(*) ? - MYSQL

Answer 2

select * from site, count(*) as count  
from myTable 
where year(created_at) = 2012
group by site having count>500 order by count DESC limit 10000

You want "having count>500" in there if you only want to select rows with more than 500 results.

WHERE doesn't work on aggregate functions.