1

The query is not working. What's wrong?

Hive Query:

create Table copy_table 
as select * from old_table as old
where column_A = 'ABC'
and column_B is null
and column_C = 'VAL'
group by column_D
order by column_E desc

Error message:

Error while compiling statement: FAILED: SemanticException [Error 10025]: Expression not in GROUP BY key p_dt
J184937
  • 67
  • 2
  • 8
  • please see https://stackoverflow.com/questions/5746687/hive-expression-not-in-group-by-key – Will Jul 17 '19 at 09:38
  • You create table as you made a query? It's confusing. You have syntax error. – dodzb Jul 17 '19 at 09:39
  • Since you accidently tagged mysql, an explanation of why you cannot do this, which is not limited to MySQL, can be found e.g. [here](https://stackoverflow.com/q/34115174) (MySQL allowed to do this for a long time and then stopped allowing it with MySQL 5.7, so a lot of questions come up asking about this specific problem) – Solarflare Jul 17 '19 at 09:45

1 Answers1

0

All columns should be aggregated or included in group by if not aggregated. And you selecting all columns * and only one is included in the group by.

Include all columns in the group by OR aggregate all other columns using min(), max(), etc. When you are doing group by, all and every column in the select should be included into aggregation or group by.

leftjoin
  • 36,950
  • 8
  • 57
  • 116