Getting ParseException when running Hive query

Question

I'm trying to find the number of employees who are paid less than average wage.

I'm pretty new to hive and struggling a bit, could someone explain whats wrong with my statement and help me out please?

My statement -

SELECT COUNT(*) FROM(SELECT wage, AVG(wage) AS avgWage FROM emp_wages) WHERE wage < avgWage;

The error -

ParseException line 1:82 cannot recognize input near 'where' 'wage' '<' in subquery source

Any help appreciated!

Answer 1

A syntax error. Derived table should be aliased.

SELECT COUNT(*) 
FROM (SELECT wage, AVG(wage) AS avgWage FROM emp_wages group by wage)  t --alias needed here
WHERE wage < avgWage;

Query wise, it needs a change.

select count(*)
from (SELECT wage, AVG(wage) over() AS avgWage 
      FROM emp_wages
     ) t
where wage < avgWage