MySQL: Using column aliases and HAVING

It isn’t possible to refer to a column alias in a WHERE clause, but you can use HAVING instead. From http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html:

This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined. For example, the following query is illegal:

SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;

The WHERE statement is executed to determine which rows should be included in the GROUP BY part, whereas HAVING is used to decide which rows from the result set should be used.

My example using HAVING:

SELECT ms.id, ms.name, COUNT( mpc.page_id ) AS count
FROM map_supplier ms
LEFT JOIN map_profile_content mpc ON ms.id = mpc.map_supplier_id
GROUP BY ms.id
HAVING count > 0