title: SQL Help: Counting Rows in a Single Query With a Nested SELECT
body: <p>I'm looking for a better way to do the following query.  I have a table that looks like this:</p>
  
  <pre><code>game_id | home_team_id | away_team_id
  1       | 100          | 200
  2       | 200          | 300
  3       | 200          | 400
  4       | 300          | 100
  5       | 100          | 400
  </code></pre>
  
  <p>And I want to write a query that counts the number of home games and away games for each team and outputs the following:</p>
  
  <pre><code>team_id | home_games | away_games
  100     | 2          | 1
  200     | 2          | 1
  300     | 1          | 1
  400     | 0          | 2
  </code></pre>
  
  <p>Right now, I wrote this monstrosity that works, but it's slow (I know it's pulling the entire 2,800 row from the table twice).</p>
  
  <pre><code>SELECT 
    home_team_id as team_id,
    (SELECT count(*) FROM `game` WHERE home_team_id = temp_game.home_team_id) as home_games,
    (SELECT count(*) FROM `game` WHERE home_team_id = temp_game.away_team_id) as away_games
    FROM (SELECT * FROM `game`) as temp_game
    GROUP BY home_team_id
  </code></pre>
  
  <p>Can a SQL guru help me knock out a better way?  I think my problem is that I don't understand how to get a distinct list of the team IDs to throw at the count queries.  I bet there's a better way with a better placed, nested SELECT.  Thanks in advance!</p>