Group by issue

[drelefun]

I’m trying to get the total score for a home and away team but not sure how to. Basically the way to get the score is goals x 6 + behinds. This only gets that quarter’s score and I want to group it by the team and game to get total score. I’m also not sure how to differentiate the team in this situation. I know it has something to do with a CASE statement but not sure how to do it.

the query below is as far as i could get. It can get the scores for each quarter but i want to group by quarters to get a final score. I also want to add the score for the away team after away.team AS Away.

SELECT AFLGame.date, AFLGame.round, home.team AS Home, AFLScore.goals * 6 + AFLScore.behinds AS Home Score, away.team AS Away, Venue.venue
FROM AFLGame INNER JOIN
Venue ON AFLGame.venue_id = Venue.venue_id INNER JOIN
Team AS home ON AFLGame.home = home.team_id INNER JOIN
Team AS away ON AFLGame.away = away.team_id INNER JOIN
AFLScore ON AFLGame.aflgame_id = AFLScore.aflgame_id

the tables involved are

AFLGame (aflGame_id, date, round, home(fk), away(fk), day_night, crowd, venue_id)
AFLScore(team_id, quarter, goals, behinds)
Team(team_id, team)

thanks for any help

 

[r937]

SELECT AFLGame.date
     , AFLGame.round
     , home.team AS Home
     , SUM(homescore.goals * 6 + homescore.behinds) AS HomeScore
     , away.team AS Away
     , SUM(awayscore.goals * 6 + awayscore.behinds) AS AwayScore
     , Venue.venue
  FROM AFLGame 
INNER
  JOIN Venue 
    ON Venue.venue_id         = AFLGame.venue_id
INNER
  JOIN Team AS home 
    ON home.team_id           = AFLGame.home
INNER
  JOIN AFLScore as homescore
    ON homescore.aflgame_id   = AFLGame.aflgame_id 
   AND homescore.team_id      = AFLGame.home
INNER
  JOIN Team AS away 
    ON away.team_id           = AFLGame.away 
INNER
  JOIN AFLScore as awayscore
    ON awayscore.aflgame_id   = AFLGame.aflgame_id 
   AND awayscore.team_id      = AFLGame.away
GROUP
    BY AFLGame.date
     , AFLGame.round
     , home.team 
     , away.team 
     , Venue.venue

r937.com | rudy.ca

 

[drelefun]

is the reason why we have

INNER JOIN AFLScore as awayscore
ON awayscore.aflgame_id = AFLGame.aflgame_id
AND awayscore.team_id = AFLGame.away

and

INNER
JOIN Team AS away
ON away.team_id = AFLGame.away

is because the primary key for the AFLScore table is aflgame_id, team_id, quarter(sorry i forgot to highlight this in my first post). i don't quite understand that bit.

i'm finding that the result is actually 4x more than the actual score should be. i can divide the score by 4 but is there something i can add to rudy's query.

the tables again are

AFLGame (aflGame_id(PK), date, round, home(fk), away(fk), day_night, crowd, venue_id)
AFLScore(aflgame_id(PK),team_id(PK), quarter(PK), goals, behinds)
Team(team_id(PK), team)
Venue(venue_id(PK), venue)

 

[r937]

the primary key for the AFLScore table is aflgame_id, team_id, quarter([red]sorry i forgot to highlight this in my first post[/red]).

that quarter would account for the fact that you're seeing 4x the totals


if a team scores 0,1,1,2 goals in the 4 quarters of a game, your table will record the quarterly scores as 0,1,1,2? or will it be 0,1,2,4 (cumulative)?

if it's the latter, then all you have to do is change this --

INNER
JOIN AFLScore as awayscore
ON awayscore.aflgame_id = AFLGame.aflgame_id
AND awayscore.team_id = AFLGame.away

to this --

INNER
JOIN AFLScore as awayscore
ON awayscore.aflgame_id = AFLGame.aflgame_id
AND awayscore.team_id = AFLGame.away
AND awayscore.quarter = 4



r937.com | rudy.ca

 

[drelefun]

the scores are not recorded cumulatively. its the former. an example of a game is below

aflgame_id team_id quarter goals behinds
2 mel 1 4 3
2 mel 2 2 0
2 mel 3 0 2
2 mel 4 3 3
2 stk 1 2 7
2 stk 2 5 0
2 stk 3 4 5
2 stk 4 2 3

 

[r937]

SELECT AFLGame.date
     , AFLGame.round
     , home.team     AS Home
     , [red]hscore.Score  AS HomeScore[/red]
     , away.team     AS Away
     , [red]ascore.Score  AS AwayScore[/red]
     , Venue.venue
  FROM AFLGame 
INNER
  JOIN Venue 
    ON Venue.venue_id         = AFLGame.venue_id
INNER
  JOIN Team AS home 
    ON home.team_id           = AFLGame.home
INNER
  JOIN [red]( SELECT aflgame_id
              , team_id
              , SUM(goals * 6 + behinds) AS Score
           FROM AFLScore 
         GROUP
             BY aflgame_id
              , team_id
       ) AS hscore[/red]
    ON hscore.aflgame_id   = AFLGame.aflgame_id 
   AND hscore.team_id      = AFLGame.home
INNER
  JOIN Team AS away 
    ON away.team_id           = AFLGame.away 
INNER
  JOIN [red]( SELECT aflgame_id
              , team_id
              , SUM(goals * 6 + behinds) AS Score
           FROM AFLScore 
         GROUP
             BY aflgame_id
              , team_id
       ) AS ascore[/red]
    ON ascore.aflgame_id   = AFLGame.aflgame_id 
   AND ascore.team_id      = AFLGame.away

r937.com | rudy.ca

 

[drelefun]

wow. i didn't expect it to be so complicated.

may i ask why this is required?

 

[r937]

to resolve the issue of quadrupling scores

r937.com | rudy.ca

 

[drelefun]

lol. cheers mate