Why Can't I Use an Aggregate Alias in a SQL HAVING Clause?

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

Consider the following SQL statement:

select col1,count(col2) as col7
from --some join operation
group by col1
having col7 >= 3 -- replace col7 by count(col2) to make the code work

This code generates an error indicating that "col7" is an invalid column name. Why is it so?

The reason lies in the execution order of SQL statements. The HAVING clause is evaluated before the SELECT clause. This means that when the HAVING clause is evaluated, the alias "col7" for the aggregate function count(col2) is not yet defined.

SQL executes queries in the following sequence:

1. Calculate the Cartesian product of all tables specified in the FROM clause.
2. Filter rows based on the WHERE clause.
3. Group rows based on the GROUP BY clause.
4. Filter groups based on the HAVING clause.
5. Evaluate expressions in the SELECT clause target list.
6. Remove duplicate rows if DISTINCT is specified.
7. Perform unions if any sub-selects are present.
8. Sort rows based on the ORDER BY clause.
9. Apply the TOP clause, if present.

Therefore, in the given code, the HAVING clause attempts to use the alias "col7" before it is defined in the SELECT clause. To resolve the issue, you can replace "col7" with the aggregate function itself:

select col1,count(col2)
from --some join operation
group by col1
having count(col2) >= 3

This allows the HAVING clause to correctly evaluate the aggregate count of col2.

The above is the detailed content of Why Can't I Use an Aggregate Alias in a SQL HAVING Clause?. For more information, please follow other related articles on the PHP Chinese website!