HAVING Clause in MySQL

Relates to MySQL and Databases

Point of note that an aggregate function is meaningless in the WHERE clause of an SQL statement. Common sense really considering the return from the function cannot be calculated until the rows have been returned. I discovered this on trying to retrieve a set of distinct categories based on the existence of at least one visible record classified in that category. The solution uses the HAVING clause.


SELECT lookup.category
FROM data LEFT JOIN lookup
ON data.cid = lookup.cid 
WHERE data.visible = 1 
GROUP BY lookup.cid 
HAVING count(data.id) > 0 
ORDER BY cname ASC

Posted on Sunday, Oct 03, 2004 at 01:47:27.

Comments on HAVING Clause in MySQL (0)

Breadcrumbs Trail

[ Home ] -> TW Blog -> Oct 04 -> HAVING Clause in MySQL
Site Map

The Severn Solutions website achieves the following standards:

[ XHTML 1.0 ] [ CSS 2 ] [ WAI AA ] [ Bobby AA ]

Page compiled in 0.126 seconds