Geek Answers Handbook

SQL Server: Selection and Counting

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 common goals for each team. A team can be either Team1IDso 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
+4
source share
3 answers

Take the following structure:

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

Show game results (not yet an answer)

-- 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;

The data is as follows:

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

Now give a rating (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

The 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 (since SQL Server Fiddle acted), the SQL statements will remain valid for SQL Server as well.

+5
source

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
+2
source

:

select t.name,
    SUM(CASE
        WHEN gt.teamSeq = 1 then s.team1Score
        WHEN gt.teamSeq = 2 then s.team2Score
    END) As Scores
from 
    team t 
    LEFT JOIN
    (select g.id,
        case 
            when g.team1id = t.id then g.team1id
            when g.team2id = t.id then g.team2id
        end as teamid,
        case 
            when g.team1id = t.id then 1
            when g.team2id = t.id then 2
        end as teamSeq
    from game g
        cross join
        team t) gt
    ON t.id = gt.teamid
    LEFT JOIN
    score s
    ON gt.id = s.gameid
where
    gt.teamid IS NOT NULL 
group by
    t.name
+1

More articles:


All Articles