How to handle hard coded values in hive group by

Question

I am running something like this and getting an error. Do I have to create the field in a separate query so I can call the field rather than the hardcoded values, or is there another way around this without the extra step?

SELECT FIELD_A
, 'NA' AS FIELD_B
, 'NA' AS FIELD_C
, MAX(PRICE) AS MAX_PRICE 
from table_xyz  
GROUP BY field_a
, 'NA'    
, 'NA'
;

Error:

OK FAILED: SemanticException [Error 10021]: Line 74:2 Repeated key in GROUP BY ''NA''

Answer 1

You can use common table expression to do this.

with t1 as 
(SELECT FIELD_A
, 'NA' AS FIELD_B
, 'NA' AS FIELD_C
, PRICE
from table_xyz)
select field_a, field_b, field_c, max(price) from t1
group by field_a, field_b, field_c

Or you can simply do

SELECT FIELD_A
, 'NA' AS FIELD_B
, 'NA' AS FIELD_C
, MAX(PRICE) AS MAX_PRICE 
from table_xyz  
GROUP BY field_a, 'NA'    

as both field_b and field_c are NA.