Aggregated Expression not in GROUP BY key

[James Pirz]

Hi,

I am using Hive 1.2, and I am trying to run some queries based on TPCH
schema. My original query is:

SELECT N_NAME, AVERAGE(C_ACCTBAL)
FROM customer JOIN nation
on C_NATIONKEY=N_NATIONKEY
GROUP BY N_NAME;

for which I get:
FAILED: SemanticException [Error 10025]: Line 1:15 Expression not in GROUP
BY key 'C_ACCTBAL'

It does not really make sense, as I am running an aggregation on an
attribute which is not part of the group-by clause, which makes sure that
each group eventually gets one single value for the output. In Hive's
language manual we see that:
( https://cwiki.apache.org/confluence/display/Hive/LanguageManual+GroupBy )

" 
 When using group by clause, the select statement can only include
columns included in the group by clause. Of course, you can have as many
aggregation functions (e.g. count) in the select statement as well."

and the example there is similar to what I have.

I even simplified the query, and dropped the join, but it did not make a
difference:

SELECT C_NATIONKEY, AVERAGE(C_ACCTBAL)
FROM customer
GROUP BY C_NATIONKEY;

FAILED: SemanticException [Error 10025]: Line 1:20 Expression not in GROUP
BY key 'C_ACCTBAL'

Can you please let me know if I am missing something here and this behavior
is expected or not ?

In case you need it, the schema for the tables looks like:

hive> describe customer;
OK
c_custkey int
c_name string
c_address string
c_phone string
c_acctbal double
c_mktsegment string
c_comment string
c_nationkey int

hive> describe nation;
OK
n_nationkey int
n_name string
n_regionkey int
n_comment string

Thanks.

[James Pirz]

Just a follow-up on the issue.
It was really happening because of using AVERAGE() instead of AVG().
Sorry, but the error was mis-leading (It did not tell me that function name
is invalid).
I had borrowed the query from a benchmark spec, and they had used AVERAGE
in their sql statements, and I failed to fix it for HiveQL.

Post by James Pirz
Hi,
I am using Hive 1.2, and I am trying to run some queries based on TPCH
SELECT N_NAME, AVERAGE(C_ACCTBAL)
FROM customer JOIN nation
on C_NATIONKEY=N_NATIONKEY
GROUP BY N_NAME;
FAILED: SemanticException [Error 10025]: Line 1:15 Expression not in GROUP
BY key 'C_ACCTBAL'
It does not really make sense, as I am running an aggregation on an
attribute which is not part of the group-by clause, which makes sure that
each group eventually gets one single value for the output. In Hive's
( https://cwiki.apache.org/confluence/display/Hive/LanguageManual+GroupBy )
" 
 When using group by clause, the select statement can only include
columns included in the group by clause. Of course, you can have as many
aggregation functions (e.g. count) in the select statement as well."
and the example there is similar to what I have.
I even simplified the query, and dropped the join, but it did not make a
SELECT C_NATIONKEY, AVERAGE(C_ACCTBAL)
FROM customer
GROUP BY C_NATIONKEY;
FAILED: SemanticException [Error 10025]: Line 1:20 Expression not in GROUP
BY key 'C_ACCTBAL'
Can you please let me know if I am missing something here and this
behavior is expected or not ?
hive> describe customer;
OK
c_custkey int
c_name string
c_address string
c_phone string
c_acctbal double
c_mktsegment string
c_comment string
c_nationkey int
hive> describe nation;
OK
n_nationkey int
n_name string
n_regionkey int
n_comment string
Thanks.