18.16 Problems with alias

You can use alias to refer to a column in the GROUP BY, ORDER BY or in the HAVING part. Aliases can also be used to give columns more better names:

SELECT SQRT(a*b) as rt FROM table_name GROUP BY rt HAVING rt > 0;
SELECT id,COUNT(*) AS cnt FROM table_name GROUP BY id HAVING cnt > 0;
SELECT id AS "Customer identity" FROM table_name;

Note that you ANSI SQL doesn't allow you to refer to an alias in a WHERE clause. This is because that when the WHERE code is executed the column value may not yet be determinated. For example the following query is illegal:

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

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