Strage behaviour of alias for aggreage function query field

sergey.sheypak566881637 — Fri, 16 Sep 2022 08:48:24 GMT

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?

Re: Strage behaviour of alias for aggreage function query field

grahn — Mon, 30 Sep 2013 04:24:44 GMT

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

sergey.sheypak566881637 — Mon, 07 Oct 2013 07:57:05 GMT

Thank you!

question Re: Strage behaviour of alias for aggreage function query field in Archives of Support Questions (Read Only)

Strage behaviour of alias for aggreage function query field

Re: Strage behaviour of alias for aggreage function query field

Re: Strage behaviour of alias for aggreage function query field