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

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.

Solution 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.

Solution 2:

The select clause is the last clause to be executed logically, except for order by. The having clause happens before select, so the aliases are not available yet.

If you really want to use an alias, not that I'd recommend doing this, an in-line view can be used to make the aliases available:

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

Or in SQL Server 2005 and above, a CTE:

; with T as (select Store_id as StoreId, count(*) as _count
    from StoreProduct
    group by Store_id)
select StoreId, _count
from T
where _count > 0

Solution 3:

You can use the alias for count in the select clause, you just can't use it in the having statement, so this would work

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