title: Hive SubQuery and Group BY
body: <p>I have two tables</p>
  
  <p>table1:</p>
  
  <pre><code>id
  1
  2
  3
  </code></pre>
  
  <p>table 2:</p>
  
  <pre><code>id date
  1   x1
  4   x2
  1   x3
  3   x4
  3   x5
  1   x6
  3   x5
  6   x6
  6   x5
  3   x6
  </code></pre>
  
  <p><strong>I want the count of each ids for table 2 that is present in table 1.</strong></p>
  
  <p>Result</p>
  
  <pre><code>id  count
  1   3
  2   0
  3   4
  </code></pre>
  
  <p>I am using this query, but its giving me error:</p>
  
  <pre><code>SELECT tab2.id, count(tab2.id)
   FROM &lt;mytable2&gt; tab2
   GROUP BY tab2.id
   WHERE tab2.id IN (select id from &lt;mytable1&gt;)
   ;
  </code></pre>
  
  <p>Error is:</p>
  
  <blockquote>
    <p>missing EOF at 'WHERE' near 'di_device_id'</p>
  </blockquote>