A.5.4. Problems with Column Aliases
You can use an alias to refer to a column in GROUP
BY, ORDER BY, or
HAVING clauses. Aliases can also be used to
give columns better names:
SELECT SQRT(a*b) AS root FROM tbl_name GROUP BY root HAVING root > 0;
SELECT id, COUNT(*) AS cnt FROM tbl_name GROUP BY id HAVING cnt > 0;
SELECT id AS 'Customer identity' FROM tbl_name;
Standard SQL doesn't allow you to refer to a column alias in a
WHERE clause. 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.
