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

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

Answer 2

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.

Answer 3

Probably because that's the way sql defines the namespaces. take, for example:

  select a as b, b as a
    from table
   where b = '5'
order by a

what do a and b refer to? The designers just chose to make the aliases only appear on the "outside" of the query.

Answer 4

You can use the alias for the aggregates in SQL, but that is just to show the alias in the results headers. But when you want to have a condition with the aggregate function in the having you still need to use the aggregate because it evaluates the function and not the name.

Answer 5

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

Answer 6

The aliases for the field names is only for naming the columns in the result, they can never be used inside the query. You can't do like this either:

select Store_id as Asdf
from StoreProduct
where Asdf = 42

However, you can safely use count(*) in both places, and the database will recognise that it's the same value, so it won't be calculated twice.