B.5.5.4 Problems with Column Aliases

An alias can be used in a query select list to give a column a
different name. You can use the alias in GROUP
BY, ORDER BY, or
HAVING clauses to refer to the column:

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 disallows references to column aliases in a
WHERE clause. This restriction is imposed
because when the WHERE clause is evaluated,
the column value may not yet have been determined. For
example, the following query is illegal:

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

The WHERE clause determines which rows
should be included in the GROUP BY clause,
but it refers to the alias of a column value that is not known
until after the rows have been selected, and grouped by the
GROUP BY.

In the select list of a query, a quoted column alias can be
specified using identifier or string quoting characters:

SELECT 1 AS `one`, 2 AS 'two';

Elsewhere in the statement, quoted references to the alias
must use identifier quoting or the reference is treated as a
string literal. For example, this statement groups by the
values in column id, referenced using the
alias `a`:

SELECT id AS 'a', COUNT(*) AS cnt FROM tbl_name
GROUP BY `a`;

But this statement groups by the literal string
'a' and will not work as expected:

User Comments

Quoted fields often result from difficulties in ordering by expressions that involve an alias. For example

SELECT group, SUM(x) AS a, SUM(y) AS b, SUM(x)+SUM(y) AS 'a+b'FROM tbl_name GROUP BY group ORDER BY `a+b`;

The column 'a+b' is only necessary because one cannot order by a + b. Backslashes may be needed if the statement is submitted from a shell. Easier names, like sum_of_a_and_b, are much less immediate to a human reader.