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?
>
