Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Strage behaviour of alias for aggreage function query field

Solved Go to solution
Highlighted

Strage behaviour of alias for aggreage function query field

Expert Contributor

Starting Impala Shell in unsecure mode
Connected to node05.kyc.megafon.ru:21000
Server version: impalad version 1.1.1 RELEASE (build 83d5868f005966883a918a819a449f636a5b3d5f)
Welcome to the Impala shell. Press TAB twice to see a list of available commands.

Copyright (c) 2012 Cloudera, Inc. All rights reserved.

(Shell build version: Impala Shell v1.1.1 (83d5868) built on Fri Aug 23 17:28:05 PDT 2013)

It works:

select cnt, msisdn from(
    select count(*) as cnt, msisdn
    from ZZZ_ROUTES_MSK

    group by msisdn
) t
where cnt > 50 and cnt < 100
order by cnt desc limit 10;

 

It doesn't work:

 

select count(*) as cnt, msisdn
from ZZZ_ROUTES_MSK

where cnt > 50 and cnt < 100

group by msisdn
order by cnt desc limit 10;

 

 

Query: select count(*) as cnt, msisdn
from ZZZ_ROUTES_MSK
group by msisdn
where cnt > 50 and cnt < 100
order by cnt desc limit 10

[localhost:21000] > select count(*) as cnt, msisdn
                  > from ZZZ_ROUTES_MSK
                  >
                  > where cnt > 50 and cnt < 100
                  >
                  > group by msisdn
                  > order by cnt desc limit 10;
Query: select count(*) as cnt, msisdn
from ZZZ_ROUTES_MSK
where cnt > 50 and cnt < 100
group by msisdn
order by cnt desc limit 10
ERROR: AnalysisException: couldn't resolve column reference: 'cnt'

 

 

Why?

 

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Strage behaviour of alias for aggreage function query field

Cloudera Employee

Why?  Because COUNT(*) is an aggregate and to filter on a aggregate you would use a HAVING clause, or use an in-line view as you have done.

 

 

2 REPLIES 2

Re: Strage behaviour of alias for aggreage function query field

Cloudera Employee

Why?  Because COUNT(*) is an aggregate and to filter on a aggregate you would use a HAVING clause, or use an in-line view as you have done.

 

 

Re: Strage behaviour of alias for aggreage function query field

Expert Contributor

Thank you!