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!

Vamsi Prabhala · Accepted Answer · 2018-11-10 04:33:52Z

2

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

edited Nov 10, 2018 at 4:33

answered Nov 9, 2018 at 20:16

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

saravanatn Over a year ago

Your 2nd HQL is right. But isn't your 1st HQL need group by for aggregation

Vamsi Prabhala Over a year ago

You’re right..edited it to be syntactically correct.

saravanatn · Accepted Answer · 2018-11-10 01:39:04Z

0

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

answered Nov 10, 2018 at 1:39

saravanatn

6305 silver badges9 bronze badges

Comments

Pang · Accepted Answer · 2018-11-10 03:07:45Z

0

The problem is AVG is an aggregation function. If you want to map one to many relations, you need to use a cross join function:

select 
  count(*), avg(v1.wage),
  sum(case when v.wage < v2.avgwage then 1 else 0 end) below_average
from 
emp_wages v cross join (select avg(wage) as avgwage from emp_wages) as v2

edited Nov 10, 2018 at 3:07

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Nov 9, 2018 at 21:58

Vin

5253 silver badges17 bronze badges

Comments

Ruben Bhattacharya · Accepted Answer · 2018-11-12 06:18:50Z

0

The correct query would be:

select count(*) where wage <(select avg(wage) from emp_wages);

You are getting a parsing error as wage and avgWage is in subquery.

answered Nov 12, 2018 at 6:18

Ruben Bhattacharya

1325 bronze badges

Collectives™ on Stack Overflow

Getting ParseException when running Hive query

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related