hadoophivehiveqlhue

Why hive is asking for my variable in group by? Simple case when


When I run the hive query bellow, I receive the following error: Error while compiling statement: FAILED: SemanticException [Error 10025]: line 8:13 Expression not in GROUP BY key '50000'

Hive Query:

SELECT 
    202106 as ANOMES,
    count(wrin_agctd) as QTDETransacoes,
    tipo_transacao,
    cod_cate_cont,
    wrin_nterm,
    case 
        when wrin_valor<50000 then '<500'
        when wrin_valor<100000 then '<1000'
        when wrin_valor<150000 then '<1500'
        when wrin_valor<200000 then '<2000'
    end as test
FROM 
    ghp00468.raultav_saque_conta_salario_tecban_202106
WHERE
    tipo_transacao="SAQUE_TECBAN" and
    tipo_transacao="CONSULTA_TECBAN"
GROUP BY 
    tipo_transacao,
    cod_cate_cont,
    wrin_nterm;

Where and what is the problem?


Solution

  • The issue is your case statement. The column test is not in the group by statement. You should add your case statement to the group by, or handle it external to the groupby. Here's a similar question that someone answered.