SQL Help: Counting Rows in a Single Query With a N

Question

I'm looking for a better way to do the following query. I have a table that looks like this:

game_id | home_team_id | away_team_id
1       | 100          | 200
2       | 200          | 300
3       | 200          | 400
4       | 300          | 100
5       | 100          | 400

And I want to write a query that counts the number of home games and away games for each team and outputs the following:

team_id | home_games | away_games
100     | 2          | 1
200     | 2          | 1
300     | 1          | 1
400     | 0          | 2

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).

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

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!

Answer 1

Here is another example.

I would point out though that you should start your from clause from the teams table, so that you'll be sure to include all the teams, even if they haven't played a game yet.

This query does your two queries as joins instead of subselects, which should perform better.

-- note: coalesce is like ifnull in case you are using mysql.

SELECT  
team_id as team_id,  
coalesce(home_game_counts.games,0) home_games,  
coalesce(away_game_counts.games,0) away_games  
FROM teams  
left join (select home_team_id, count(*) games from games group by home_team_id) as   home_game_counts on home_game_counts.home_team_id = teams.team_id  
left join (select away_team_id, count(*) games from games group by away_team_id) as   away_game_counts on away_game_counts.away_team_id = teams.team_id  
GROUP BY teams.team_id, home_game_counts.games ,  
away_game_counts.games   

Answer 2

declare @ts table

(
    team_id int
)

declare @t table
(
    id int,
    h int,
    a int
)

insert into @ts values (100)
insert into @ts values (200)
insert into @ts values (300)
insert into @ts values (400)

insert into @t values (1, 100, 200)
insert into @t values (2, 200, 300)
insert into @t values (3, 200, 400)
insert into @t values (4, 300, 100)
insert into @t values (5, 100, 400)

select s.team_id, t0.home, t1.away
from @ts s
    left outer join (select team_id, count(h) as [home] from @ts inner join @t on h = team_id group by team_id) t0 on t0.team_id = s.team_id
    left outer join (select team_id, count(a) as away from @ts inner join @t on a = team_id group by team_id) t1 on t1.team_id = s.team_id