SQL Help: Counting Rows in a Single Query With a Nested SELECT

It's cleaner if you have another table team with team_id and team_name.

SELECT team_id, team_name, 
     sum(team_id = home_team_id) as home_games, 
     sum(team_id = away_team_id) as away_games
 FROM game, team
 GROUP BY team_id

What's going on: the no WHERE clause causes a Cartesian Product between the two tables; we group by team_id to get back to one row per team. Now there are all the rows from the game table for each team_id so you need to count them but the SQL count function isn't quite right (it would count all the rows or all the distinct rows). So we say team_id = home_team_id which resolves to 1 or 0 and we use sum to add up the 1's.

The team_name is just because it's geeky to say that 'team 200 had 20 home games' when we ought to say that 'Mud City Stranglers had 20 home games'.

PS. this will work even if there are no games (often a problem in SQL where there is a team with 0 games and that row will not show up because the join fails).


If you want the distinct list of teams, you have to select from the game table twice, unioning the home and the away teams (theoretically, one team could play all its games on the road or at home, if you have logic that prevents that, then you could adjust this query):

select home_team_id as team_id from game union
select away_team_id as team_id from game

The union operator will make sure you only get distinct elements in the return set (unless you use union all)

From there, you can use left outer joins to aggregate your data:

select
    u.team_id, count(h.game_id) as home_games, count(a.game_id) as away_games
from
    (
        select home_team_id as team_id from game union
        select away_team_id as team_id from game
    ) as u
        left outer join game as h on h.home_team_id = u.team_id
        left outer join game as a on a.away_team_id = u.team_id
group by
    u.team_id

If you want to reduce your table scans even further (the above will produce four), you can add more code, but it will cost you. You can get a list of rows with the team_id, and whether or not the game was played at home or away:

select
    case ha.home when 0 then g.away_team_id else g.home_team_id end as team_id,
    case ha.home when 0 then 0 else 1 end as home_games,
    case ha.home when 0 then 1 else 0 end as away_games
from
    game as g, (select 0 as home union select 1 as home) as ha

From there, you can simply sum up the games at home and away for each team:

select
    t.team_id, sum(t.home_games) as home_games, sum(t.away_games) as away_games
from
    (
        select
            case ha.home when 0 then g.away_team_id else g.home_team_id end as team_id,
            case ha.home when 0 then 0 else 1 end as home_games,
            case ha.home when 0 then 1 else 0 end as away_games
        from
            game as g, (select 0 as home union select 1 as home) as ha
    ) as t
group by
    t.team_id

This will result in a single table scan.


Greg,

I think your ultimate solution will be language-specific. But if you were doing this in Oracle, you could query the table only once with the following:

SELECT game.home_team_id AS team_id,
       SUM(CASE WHEN game.home_team_id = game.away_team_id
                THEN 1
                ELSE 0 END) AS home_games,
       SUM(CASE WHEN game.home_team_id <> game.away_team_id
                THEN 1
                ELSE 0 END) AS away_games
  FROM game
GROUP BY game.home_team_id
ORDER BY game.home_team_id;

You don't say what flavor of SQL you're using so this is the best I can do.

Best of luck,

Stew

p.s. It looks like I've given the same solution as MarlonRibunal. I just didn't have a handy link so had to create the code by hand. :-/

Tags:

Sql

Recent Posts