Query error: Failed to recognize predicate 'group'. Failed rule: 'identifier' in subquery source

Question

I tried the below query in hive. I get the error "Failed to recognize predicate 'group'. Failed rule: 'identifier' in subquery source".

From my understanding, I am not sure on what does that error mean in the below query. Any suggestions would be great !!

select val1, val2, count(distinct(val3)) from (
    select val1, val2, val3
    from tab1
    where (val1 in ('A', 'B') 
    or val2 in ('C', 'D')))
group by val1, val2

Answer 1

You have to alias your sub-query:

select val1, val2, count(distinct(val3)) from (
    select val1, val2, val3
    from tab1
    where (val1 in ('A', 'B') 
    or val2 in ('C', 'D'))) as t
group by val1, val2