hive Expression Not In Group By Key

Question

I create a table in HIVE. It has the following columns:

id bigint, rank bigint, date string

I want to get avg(rank) per month. I can use this command. It works.

select a.lens_id, avg(a.rank)
from tableA a
group by a.lens_id, year(a.date_saved), month(a.date_saved); 

However, I also want to get date information. I use this command:

select a.lens_id, avg(a.rank), a.date_saved
from lensrank_archive a
group by a.lens_id, year(a.date_saved), month(a.date_saved);

It complains: Expression Not In Group By Key

Answer 1

A walk around is to put the additional field in a collect_set and return the first element of the set. For example

select a.lens_id, avg(a.rank), collect_set(a.date_saved)[0]
from lensrank_archive a
group by a.lens_id, year(a.date_saved), month(a.date_saved);

Answer 2

This is because there is more than one ‘date_saved’ record under your group by. You can turn these ‘date_saved’ records into arrays and output them.

Answer 3

The full error message should be in the format Expression Not In Group By Key [value].
The [value] will tell you what expression needs to be in the Group By.

Just looking at the two queries, I'd say that you need to add a.date_saved explicitly to the Group By.

Answer 4

I was also facing the same problem. If you are trying to execute the query using beeline then write your query in lowercase. Like this :

select column_name(s)
from table_name
where condition
group by column_name(s).

Writing it in lowercase worked for me try it I think it will work.