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.
On Wed, Jul 29, 2015 at 5:03 PM, James Pirz <james.p...@gmail.com> wrote: > 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. >
