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

Question

I was wondering why can't I use alias in a count(*) and reference it in the having clause. For instance:

select Store_id as StoreId, count(*) as _count     from StoreProduct     group by Store_id         having _count > 0 

Wouldn't work.. But it works if I remove _count and use count(*) instead.

Answer 1

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.