Why can't I use an alias for an aggregate in a having clause?
The HAVING
clause is evaluated before the SELECT
- so the server doesn't yet know about that alias.
First, the product of all tables in the
FROM
clause is formed.The
WHERE
clause is then evaluated to eliminate rows that do not satisfy the search_condition.Next, the rows are grouped using the columns in the
GROUP BY
clause.Then, groups that do not satisfy the
search_condition
in theHAVING
clause are eliminated.Next, the expressions in the
SELECT
statement target list are evaluated.If the
DISTINCT
keyword in present in the select clause, duplicate rows are now eliminated.The
UNION
is taken after each sub-select is evaluated.Finally, the resulting rows are sorted according to the columns specified in the
ORDER BY
clause.TOP
clause is executed.
Hope this answers your question. Also, it explains why the alias works in ORDER BY
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.
Try with this one as the select list contains the same expression you can use in having clause also:
SELECT COL1,COUNT(COL2) AS COL7
FROM --SOME JOIN OPERATION
GROUP BY COL1
HAVING COUNT(COL2) >= 3