CODEWITHSUNDEEP
mysqlhadoophive
kartik
kartik

Reputation: 41

GROUP BY and JOIN in HIVE

How can I do something like this in HIVE:

Table1:

ID  Name        Friends
1   Tom         5

Table2:

ID  Name        DOB
1   Jerry       10/10/1999
1   Kate        Null
1   Peter       02/11/1983
1   Robert      Null
1   Mitchell    09/09/2000

What I want to do is:

For each ID in table 1, find num of not null DOB and then divide by Friends

I wrote a query as:

SELECT t.ID, t.Friends, COUNT(s.DOB)/ t. Friends from Table1 t join Table2 s on (t.ID = s.ID) GROUP BY t.ID

When I do this, I get the error as FRIENDS is not part of the GROUP BY Key

The answer I am expecting is: 3/5

Upvotes: 1

Views: 4538

Answers (2)

user4905630
user4905630

Reputation: 31

if you have declare id,friends in table 1 as integer and id in table2 as integer, then below query will get you intended output

select a.name, concat(cast(b.cnt as string),'/',cast(a.friends as string)) 
from table1 a 
join 
    (select id, count(DOB) as cnt from table2 where DOB is not null group by id) b 
on (a.id = b.id)

Upvotes: 0

Tim3880
Tim3880

Reputation: 2583

Just add the FRIENDS to your GROUP BY section:

 SELECT t.ID, t.FRIENDS, COUNT(s.DOB)/ t. FRIENDS 
 from Table1 t 
 join Table2 s      
 on (t.ID = s.ID) 
 GROUP BY t.ID. t.FRIENDS

I prefer to write this kind of query like this:

 SELECT t.ID, case when t.FIREND>0 then Cnt / t.FRIENDS ELSE 0 END 
 FROM Table t1 
 JOIN (Select ID, Count(*) AS Cnt  from Table 1 GROUP BY Id)  t2
 ON t1.ID = t2.ID

Upvotes: 1

Related Questions