SQL Server: Select and count

Question

I have 3 tables:

> HockeyTeam with columns: ID and Name 
> HockeyGame with columns: ID,Team1ID and Team2ID (both linked to HockeyTeam ID) 
> GameScores with columns: ID, GameID, Team1Score, Team2Score

HockeyTeam

ID   Name
1    Monkeys
2    Beavers
3    Dolphins
4    Snakes

GameScore

ID   GameID   Team1Score   Team2Score
1    1        3            2
2    2        4            0
3    3        2            3
4    4        2            4

HockeyGame

ID   StartTime   Team1ID   Team2ID
1    2/6/2015    1         2
2    2/13/2015   3         4
3    2/20/2015   2         4
4    2/27/2015   1         3

I need to display total goals for each team. A team can be in both Team1ID or Team2ID. Here is what I got so far:

SELECT  
    ht.Name, 
    SUM(SELECT (gs.Team1Score + gs.Team2Score) 
        FROM GameScores as gs
            INNER JOIN HockeyGame as hg
            INNER JOIN  HockeyTeam  as ht
            ON ht.ID = hg.Team1ID OR ht.ID = ht.Team2ID
            ON gs.GameID = hg.ID
FROM 
    HockeyTeam  as ht

Answer 1

Let's take a structure like this below:

create table team (id int, name varchar(20));
insert into team values (1, 'H1'), (2, 'H2'), (3, 'H3');

create table game (id int, team1id int, team2id int);
insert into game values (11, 1, 2), (12, 1, 3), (13, 2, 3);

create table score (
  id int,
  gameid int,
  team1score int,
  team2score int
);
insert into score values 
(21, 11, 5, 2), 
(22, 12, 2, 5), 
(23, 13, 0, 2);  

Display game results (not the answer yet)

-- show readable game results
select
  s.gameid,
  t1.name as team1,
  t2.name as team2,
  team1score,
  team2score
from score s
inner join game g on s.gameid = g.id
inner join team t1 on g.team1id = t1.id
inner join team t2 on g.team2id = t2.id;

Data looks like this:

gameid  team1  team2  team1score  team2score
11      H1     H2     5           2
12      H1     H3     2           5
13      H2     H3     0           2

Let's get the scores now (the answer)

-- show score by team 
select
  t.name,
  sum(score) as goals
from team t

left join 
(
  -- get score of team1
  select
    t1.id as teamid,
    sum(team1score) as score
  from score s
  inner join game g on s.gameid = g.id
  inner join team t1 on g.team1id = t1.id
  group by t1.id

  union all

  -- get score of team2 and combine it with results from team1
  -- this is because team2 can be team1 for some games
  select
    t2.id as teamid,
    sum(team2score) as score
  from score s
  inner join game g on s.gameid = g.id
  inner join team t2 on g.team2id = t2.id
  group by t2.id
) t1 on t.id = t1.teamid

group by t.name

Result will look like this:

Name  Goals
H1    7
H2    2
H3    7

Example: http://sqlfiddle.com/#!9/aa3cc/15 Although the example is for MySQL (because SQL Server Fiddle was acting up), the SQL statements will remain valid for SQL Server also.

Answer 2

You can do this without unions:

select t.id, t.name,
sum(case when oa.team1id = t.id then oa.team1score else 0 end +
    case when oa.team2id = t.id then oa.team2score else 0 end)
from team t
outer apply(select g.team1id, g.team2id, s.team1score, s.team2score 
            from game g 
            join score s on g.id = s.gameid
            where team1id = t.id or team2id = t.id) oa
group by t.id, t.name