CODEWITHSUNDEEP
sqlhadoophive
Nimit
Nimit

Reputation: 1712

Hive : How to perform JOIN Operation with GROUP BY

I have two tables 

A -- > id , name
B -- > id , value

In B table, I have multiple duplicate id, I want to find average of particular id and print id , name , AVG(value)

Here is my query for finding average in table B

SELECT id, AVG(value) FROM B GROUP BY id;

I need to perform JOIN operation, How to perform JOIN Operation with GROUP BY.

Upvotes: 0

Views: 1576

Answers (1)

DLeh
DLeh

Reputation: 24395

Here's what you want: you want to group by both the id and the name

SELECT a.id, a.name, avg(b.value)
FROM A
     JOIN B ON A.id = B.id
GROUP BY A.id, A.name

Upvotes: 1

Related Questions