I have a simple query like this (identify hosts that have more than one
request):

select host, count(1) as cnt from accesslogs where cnt>1 group by host;

and it throws the error Invalid Table Alias or Column Reference cnt.

Is using column alias like this unsupported? Is there another way of
achieving the same query as above?

The SQL way of doing it is with a HAVING clause. But hive does not support
it yet. You can use a sub-query to do the same.

select a.host, a.cnt
from (select host, count(1) as cnt
      from accesslogs group by host) a
where a.cnt > 1
On 8/20/09 6:55 PM, "Vijay" <[EMAIL PROTECTED]> wrote:

> I have a simple query like this (identify hosts that have more than one
> request):
>
> select host, count(1) as cnt from accesslogs where cnt>1 group by host;
>
> and it throws the error Invalid Table Alias or Column Reference cnt.
>
> Is using column alias like this unsupported? Is there another way of achieving
> the same query as above?
>