MySQL Having Clause
Tuesday, September 25th, 2007Last Friday, I had to run some complicated SQL queries at work. I was dealing with 300k rows of history items that I had to select based on an aggregate function. Unfortunately, WHERE clauses cannot contain the result of an aggregate function. Having clauses are like where clauses, but they’re applied last, and without optimization–and they can contain the result of an aggregate function. Here’s an example:
mysql> SELECT `col1`, COUNT(distinct `col2`) as as distcol2 , COUNT(distinct HOUR(`date`)) as hours FROM `sometable` WHERE foo GROUP BY `col1` HAVING `hours` = 2 and `distcol2` = 2 order by rand() DESC limit 100;