CODEWITHSUNDEEP
hive
User12345
User12345

Reputation: 325

Hive - nested query

I've totally new to hive. I've able to run non-nested SQL queries without a problem. However, I'm unable to execute the below query.

SELECT E_DATE,
       CLIENT,
       INS,
       (SELECT SUM(time)
        FROM   mytable b
        WHERE  a.E_DATE = b.E_DATE
               AND a.INS = b.INS
               AND b.CLIENT = 'comA') TOTAL_TIME
FROM   mytable a
GROUP  BY E_DATE,CLIENT,INS

I get the error FAILED: ParseException line 4:8 cannot recognize input near 'SELECT' 'SUM' '(' in expression specification

Upvotes: 0

Views: 492

Answers (1)

Ankit Agrahari
Ankit Agrahari

Reputation: 379

select E_DATE,CLIENT,INS,SUM(time) 
from mytable 
where CLIENT = 'comA' 
group by E_DATE,CLIENT,INS;

OR

select a.E_DATE,a.CLIENT,a.INS,SUM(b.time)
from mytable a JOIN mytable b ON (a.E_DATE = b.E_DATE AND a.INS = b.INS)
where b.CLIENT = 'comA'
group by a.E_DATE,a.CLIENT,a.INS;

If you have a nested queries in sql, than in hive you have to go for joins to get the result.

Upvotes: 1

Related Questions