title: GROUP BY and JOIN in HIVE
body: <p>How can I do something like this in HIVE:</p>
  
  <p>Table1:</p>
  
  <pre><code>ID  Name        Friends
  1   Tom         5
  </code></pre>
  
  <p>Table2:</p>
  
  <pre><code>ID  Name        DOB
  1   Jerry       10/10/1999
  1   Kate        Null
  1   Peter       02/11/1983
  1   Robert      Null
  1   Mitchell    09/09/2000
  </code></pre>
  
  <p>What I want to do is:</p>
  
  <p>For each ID in table 1, find num of not null DOB and then divide by Friends</p>
  
  <p>I wrote a query as:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>When I do this, I get the error as FRIENDS is not part of the GROUP BY Key</p>
  
  <p>The answer I am expecting is: 3/5</p>