Why can't I use an alias in the count (*) "column and refer to it in the presence condition?

Question

I was wondering why I cannot use the alias in count (*) and refer to it in the having clause. For example:

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

Does not work. But it works if I remove _count and use count (*).

Answer 1

See the referenced document, CodeByMoonlight , answer your recent question .

A HAVING clause evaluates to SELECT - so the server does not yet know about this alias.

• First, a product of all the tables in from is created .
• The where clause is evaluated to exclude strings that do not satisfy the search_condition clause.
• Then the rows are grouped using the columns in the group .
• Then, groups that do not satisfy the search_condition clause in the sentence .
• Further, the expressions in the select target list are evaluated.
• If the excellent keyword is present in the select clause, duplicate lines are now eliminated.
• union is executed after evaluating each subsample.
• Finally, the resulting rows are sorted by the columns specified in order .

Answer 2

The select clause is the last clause that should be executed logically, with the exception of order by . The having occurs before the selection, so aliases are not yet available.

If you really want to use an alias, and not what I would recommend doing this, you can use the built-in view to get the available aliases:

 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, 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 3

You can use an alias for count in the select clause, you just cannot use it in the statement, so this will work

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

Answer 4

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

Answer 5

Aliases for field names are only intended to indicate columns as a result; they can never be used inside a query. You also cannot do this:

 select Store_id as Asdf from StoreProduct where Asdf = 42 

However, you can safely use count(*) in both places, and the database will recognize the same value, so it will not be calculated twice.

Answer 6

In Hive 0.11.0 and later, columns can be specified by position if hive.groupby.orderby.position.alias is set to true.

 set hive.groupby.orderby.position.alias=true; select Store_id as StoreId, count(*) as _count from StoreProduct group by 1 

I do not understand the purpose of your request. Given the context of the request you sent, your condition is not necessary, because elements that do not exist, i.e. e. count 0 will never be the result of a query ...

Answer 7

Here is my contribution (based on the code posted here):

 select * from ( SELECT Store_id as StoreId, Count(*) as StoreCount FROM StoreProduct group by Store_id ) data where data.StoreCount > 0 

Answer 8

Perhaps because the sql method defines namespaces. take for example:

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

What do a and b say? Designers simply decided that nicknames should only be displayed "outside" the request.