June 26, 2008 at 2:10 am
I have a player stats table which I want to get the team average but I can only get the average for each player. The way to get the team average is dividing the sum of the player stats and then dividing by the number of games a team has played. I know how to get the total of the stats but am unsure how to execute the dividing the number of games. Here’s my query for getting the sum of each stat. I guess the part I’m confused with is that there is a home and away team and I’m unsure how to differentiate the two or combining them.
Just on a side note is there a name for these type queries which involve the home and away column as I have a lot of trouble getting my head around it. If anyone knows any good websites or references with similar situations could you let me know as I don’t know how to search for it.
Here is my sum stats query below if that helps
SELECT Team.team, SUM(AFLPlayerStats.kicks) AS Kicks, SUM(AFLPlayerStats.marks) AS Marks, SUM(AFLPlayerStats.handballs) AS Handballs
FROM AFLPlayerStats INNER JOIN
Team ON AFLPlayerStats.team_id = Team.team_id INNER JOIN
AFLGame ON AFLGame.aflgame_id = AFLPlayerStats.aflgame_id
GROUP BY Team.team
I want the final table to show the team averages
TeamKicksMarksHandballs
Sydney Swans160100240
Brisbane Lions14480260
The tables involved are
AFLGame (aflGame_id(pk), date, round, home(fk), away(fk), day_night, crowd, venue_id)
AFLPlayerStats(aflGame_id(pk), team_id(pk), player_id(pk), kicks, marks, handballs, goals, behinds, hitouts, tackles, freesFor, freesAgainst)
Team(team_id(pk), team)
June 26, 2008 at 4:04 am
Hi,
Are you looking for this result:
SELECT Team.team,
SUM(AFLPlayerStats.kicks)/Count(distinct AFLPlayerStats.aflGame_id) AS Kicks,
SUM(AFLPlayerStats.marks)/Count(distinct AFLPlayerStats.aflGame_id) AS Marks,
SUM(AFLPlayerStats.handballs)/Count(distinct AFLPlayerStats.aflGame_id) AS Handballs
FROM AFLPlayerStats INNER JOIN Team ON AFLPlayerStats.team_id = Team.team_id
INNER JOIN AFLGame ON AFLGame.aflgame_id = AFLPlayerStats.aflgame_id
GROUP BY Team.team
June 26, 2008 at 6:42 am
Thanks for that.
My first post wasn't very clear but what if I just wanted the home or away team averages. I can't wrap my head around how to do such a query.
June 26, 2008 at 6:56 am
Should be fairly straight foward.
Could you post a bit of sample data for your tables ?
June 26, 2008 at 7:12 am
yeah you were right. it is straight forward.
Here's the away averages query.
SELECT Team.team, SUM(AFLPlayerStats.kicks) / COUNT(DISTINCT AFLGame.aflgame_id) AS Kicks, SUM(AFLPlayerStats.marks)
/ COUNT(DISTINCT AFLGame.aflgame_id) AS Marks, SUM(AFLPlayerStats.handballs) / COUNT(DISTINCT AFLGame.aflgame_id) AS Handballs
FROM AFLPlayerStats INNER JOIN
AFLGame ON AFLPlayerStats.team_id = AFLGame.away AND AFLGame.aflgame_id = AFLPlayerStats.aflgame_id INNER JOIN
Team ON Team.team_id = AFLGame.away
GROUP BY Team.team
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply