title: SQL Server : printing all the rows from left table while using aggregate operator COUNT()
body: <p>I am trying to write a query that returns a count of <code>TotalPlayers</code> in each team who scored less than 10 runs as <code>[LowScoringPlayers]</code>, if all the players in a team scored more than 10 runs, I still need to print the <code>TeamName</code> and 0 as the <code>[LowScoringPlayers]</code> for that team.</p>
  <p>My below query gives me the count correctly but if there are no players in a team who scored less than 10 runs then it skips printing that <code>TeamName</code>.</p>
  <pre><code>SELECT DISTCINT
      (TM.[TeamId]), TM.[TeamName], 
      COUNT(DISTINCT S.[PlayerId]) AS [LowScoringPlayers]
  FROM 
      dbo.[Teams] TM
  INNER JOIN 
      dbo.[Player] PL ON PL.[PlayerId] = TM.[TeamId]
  INNER JOIN 
      dbo.[ScoreAudit] S ON S.[PlayerId] = PL.[PlayerId] AND S.[Runs] &lt;= 10
  GROUP BY 
      TM.[TeamId], TM.[TeamName]
  </code></pre>
  <p>If I remove the condition <code>S.[Runs] &lt;=10</code> the query prints all the team names.. 1 way I could think of is using a case statement before <code>S.[Runs] &lt;=10</code>, but the data is huge so there might be performance issues.</p>
  <p>Any other better way?</p>