Why Can't I Use Alias in a Count(*) "Column" and Reference It in a Having Clause

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.

Why can't I use an alias for an aggregate in a having clause?

In MS SQL, the only place (I'm aware of) that you can reference aliases is in the ORDER BY clause. The ability to reference aliases in other parts of the query is a feature that many other db platforms have and honestly it annoys me that Microsoft hasn't considered it a useful enough feature to add it.

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;

PostgreSQL - Aliases column and HAVING

Wrap it into a derived table:

SELECT CASE 
          WHEN PJZ = 0 THEN 100
          ELSE PJZ
       END as PJZ,
       mass
FROM (
    SELECT CASE 
             WHEN SUM(X.Count)*3600 is null THEN '0'  
             ELSE SUM(X.Count)*3600  
           END AS PJZ,  
           X.Mass  
    FROM X  
    WHERE X.Mass > 2000  
    GROUP BY X.mass
) t
WHERE PJZ = 0 
   OR ((X.Mass / PJZ * 100) - 100) >= 10;

(Note that I added the missing group by as otherwise the query would not be valid)

Mysql: count as alias with where or having

WHERE 1 = 1 AND 'num_installazioni' < 5

You are not using an alias here - you are checking if the text literal num_installazioni is “less than” 5.

https://dev.mysql.com/doc/refman/5.7/en/identifiers.html

It “works” as in it does not throw an error, because you did not use an alias in the WHERE clause here (which you can’t.) If you think this gives you a result that makes any sense however, you are completely in the wrong here.

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 hive recognize alias named in select part?

The where clause is evaluated before the select clause, which is why you can't refer to select aliases in your where clause.

You can however refer to aliases from a derived table.

select * from (
  select user as u1, url as u2 from rank_test
) t1 where u1 <> "";

select * from (
  select user, count(*) as cnt from rank_test group by user
) t1 where cnt >= 2;

Side note: a more efficient way to write the last query would be

select user, count(*) as cnt from rank_test group by user
having count(*) >= 2

If I remember correctly, you can refer to the alias in having i.e. having cnt >= 2

(T-SQL) Why does this subquery need an alias?

The alias after the subquery (or derived table, if you prefer) is required by SQL Server. It is not only a requirement but a really good idea. In general, column references should be qualified, meaning that they include a table alias. Without an alias, references to columns in the subquery could not be qualified. I think that's a bad thing.

SQL Server is not the only database that requires the alias. MySQL and Postgres (and hence most Postgres-derived databases) do as well. Oracle and SQLite do not. Nor does Google's BigQuery.

I do not know if the alias is an ANSI/ISO requirement. However, I always use one, regardless of the database.

invalid column name' while using the HAVING

Aggregation is required , as you have no access to alias total_calories

SELECT   type,SUM(calories) AS total_calories 
FROM     exercise_logs 
GROUP BY type 
HAVING   SUM(calories) > 150;

---

Related Topics