title: Hive Script, DISTINCT with SUM
body: <p>I am trying to distinct and then find the count of the teams a player played for in any single season and number of teams he played for. This is tripping me up and ofcourse i have a sample down below(2nd) one. The first ones is my failed attempt</p>
  
  <pre><code>SELECT o.id,o.year,COUNT(DISTINCT(o.team)) b JOIN
  (SELECT id, year, team FROM batting
  GROUP BY id,year,team
  ORDER BY id DESC
  LIMIT 25) o 
  0.id =b.id;
  
  
  
  SELECT id, year, team FROM batting
  GROUP BY id,year,team
  ORDER BY id DESC
  LIMIT 25;
  </code></pre>
  
  <p>produces
  <a href="https://i.stack.imgur.com/SVxXJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SVxXJ.png" alt="enter image description here"></a></p>
  
  <p>IGNORE the ^A, i think they represent either space or comma, just column seperatpr</p>