Why Can't I Use Column Aliases in the Next Select Expression

Why can't I use column aliases in the next SELECT expression?

You can use a previously created alias in the GROUP BY or HAVING statement but not in a SELECT or WHERE statement. This is because the program processes all of the SELECT statement at the same time and doesn't know the alias' value yet.

The solution is to encapsulate the query in a subquery and then the alias is available outside.

SELECT stddev_time, max_time, avg_time, min_time, cnt, 
       ROUND(avg_time * cnt, 2) as slowdown
FROM (
        SELECT 
            COALESCE(ROUND(stddev_samp(time), 2), 0) as stddev_time, 
            MAX(time) as max_time, 
            ROUND(AVG(time), 2) as avg_time, 
            MIN(time) as min_time, 
            COUNT(path) as cnt, 
            path
        FROM 
            loadtime
        GROUP BY
            path
        ORDER BY
            avg_time DESC
        LIMIT 10
   ) X;

Why can't i refer to a column alias in the ORDER BY using CASE?

This has to do with how a SQL dbms resolves ambiguous names.

I haven't yet tracked down this behavior in the SQL standards, but it seems to be consistent across platforms. Here's what's happening.

create table test (
  col_1 integer,
  col_2 integer
);

insert into test (col_1, col_2) values 
(1, 3), 
(2, 2), 
(3, 1);

Alias "col_1" as "col_2", and use the alias in the ORDER BY clause. The dbms resolves "col_2" in the ORDER BY as an alias for "col_1", and sorts by the values in "test"."col_1".

select col_1 as col_2
from test
order by col_2;

col_2
--
1
2
3

Again, alias "col_1" as "col_2", but use an expression in the ORDER BY clause. The dbms resolves "col_2" not as an alias for "col_1", but as the column "test"."col_2". It sorts by the values in "test"."col_2".

select col_1 as col_2
from test
order by (col_2 || '');

col_2
--
3
2
1

So in your case, your query fails because the dbms wants to resolve "NewValue" in the expression as a column name in a base table. But it's not; it's a column alias.

PostgreSQL

This behavior is documented in PostgreSQL in the section Sorting Rows. Their stated rationale is to reduce ambiguity.

Note that an output column name has to stand alone, that is, it cannot be used in an expression — for example, this is not correct:

SELECT a + b AS sum, c FROM table1 ORDER BY sum + c;          -- wrong

This restriction is made to reduce ambiguity. There is still ambiguity if an ORDER BY item is a simple name that could match either an output column name or a column from the table expression. The output column is used in such cases. This would only cause confusion if you use AS to rename an output column to match some other table column's name.

Documentation error in SQL Server 2008

A slightly different issue with respect to aliases in the ORDER BY clause.

If column names are aliased in the SELECT list, only the alias name can be used in the ORDER BY clause.

Unless I'm insufficiently caffeinated, that's not true at all. This statement sorts by "test"."col_1" in both SQL Server 2008 and SQL Server 2012.

select col_1 as col_2
from test
order by col_1;

Referring to a Column Alias in a WHERE Clause

SELECT
   logcount, logUserID, maxlogtm,
   DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE ( DATEDIFF(day, maxlogtm, GETDATE() > 120)

Normally you can't refer to field aliases in the WHERE clause. (Think of it as the entire SELECT including aliases, is applied after the WHERE clause.)

But, as mentioned in other answers, you can force SQL to treat SELECT to be handled before the WHERE clause. This is usually done with parenthesis to force logical order of operation or with a Common Table Expression (CTE):

Parenthesis/Subselect:

SELECT
   *
FROM
(
   SELECT
      logcount, logUserID, maxlogtm,
      DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
   FROM statslogsummary   
) as innerTable
WHERE daysdiff > 120

Or see Adam's answer for a CTE version of the same.

SQL not recognizing column alias in where clause

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.

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.

So, the following query is illegal:

SQL> SELECT empno AS employee, deptno AS department, sal AS salary
  2  FROM emp
  3  WHERE employee = 7369;
WHERE employee = 7369
      *
ERROR at line 3:
ORA-00904: "EMPLOYEE": invalid identifier


SQL>

The column alias is allowed in:

• GROUP BY
• ORDER BY
• HAVING

You could refer to the column alias in WHERE clause in the following cases:

1. Sub-query
2. Common Table Expression(CTE)

For example,

SQL> SELECT * FROM
  2  (
  3  SELECT empno AS employee, deptno AS department, sal AS salary
  4  FROM emp
  5  )
  6  WHERE employee = 7369;

  EMPLOYEE DEPARTMENT     SALARY
---------- ---------- ----------
      7369         20        800

SQL> WITH DATA AS(
  2  SELECT empno AS employee, deptno AS department, sal AS salary
  3  FROM emp
  4  )
  5  SELECT * FROM DATA
  6  WHERE employee = 7369;

  EMPLOYEE DEPARTMENT     SALARY
---------- ---------- ----------
      7369         20        800

SQL>

Using column alias in WHERE clause of MySQL query produces an error

You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses.

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.

Copied from MySQL documentation

As pointed in the comments, using HAVING instead may do the work. Make sure to give a read at this question too: WHERE vs HAVING.

Why can't I use alias in a count(*) column and reference it in a having clause?

See the document referenced by CodeByMoonlight in an answer to your recent question.

The HAVING clause is evaluated before the SELECT - so the server doesn't yet know about that alias.

1. First the product of all tables in the from clause is formed.
2. The where clause is then evaluated to eliminate rows that do not satisfy
   the search_condition.
3. Next, the rows are grouped using the columns in the group by clause.
4. Then, Groups that do not satisfy the search_condition in the having
   clause are eliminated.
5. Next, the expressions in the select clause target list are
   evaluated.
6. If the distinct keyword in present in the select clause, duplicate rows
   are now eliminated.
7. The union is taken after each sub-select is evaluated.
8. Finally, the resulting rows are sorted according to the columns
   specified in the order by clause.

alias fields Invalid column name

It seem you are working with SQL server if so, then you can use apply to avoid repeat same expression :

select t.firstdate, t.lastdate, tt.alias1,
       (case when alias1 > 15 then 'a' else 'b' end) as alias2
from table1 t cross apply
     ( values (t.lastdate - t.firstdate) 
     ) tt(alias1);

---

Related Topics