Why I am getting error while performing group by in hive?

I am executing below command in hive:

Select child.data_volume_gprs_dl + child.data_volume_gprs_ul as data_usage,
       parent.file_name,
       parent.record_number
  from table1 as parent
  left join table2 child
    on parent.file_name = child.file_name
   and parent.record_number = child.record_number
 where parent.served_imsi like '310120%'
    or parent.served_imsi like '312530%'
   and parent.serving_node_plmn_id like '310260%'
   and parent.date_part = 20191201
 group by parent.file_name, parent.record_number

​x
 
Select child.data_volume_gprs_dl + child.data_volume_gprs_ul as data_usage,       parent.file_name,       parent.record_number  from table1 as parent  left join table2 child    on parent.file_name = child.file_name   and parent.record_number = child.record_number where parent.served_imsi like '310120%'    or parent.served_imsi like '312530%'   and parent.serving_node_plmn_id like '310260%'   and parent.date_part = 20191201 group by parent.file_name, parent.record_number​

Error: Error while compiling statement: FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key ‘data_volume_gprs_dl’ (state=42000,code=10025)

Why I am getting this error?

Answer

All non-aggregated columns should be in the GROUP BY list such as

 group by parent.file_name, parent.record_number,
          child.data_volume_gprs_dl, child.data_volume_gprs_ul

 
 group by parent.file_name, parent.record_number,          child.data_volume_gprs_dl, child.data_volume_gprs_ul​

If you need to group by parent.file_name and parent.record_number columns only, then you can apply aggregation ( sum(), avg(), count() ) functions for the rest of the columns. Even addition sum(child.data_volume_gprs_dl + child.data_volume_gprs_ul) might be possible.

Advertisement

Answer