averages problem

[phunkyb]

Hi everyone
I'm a newbie to access and i've been stumped on this query for a while now. The query reads as this

SELECT Teams.Team, Avg([Game Percentages].[FG%]) AS [AvgOfFG%], Avg([Game Percentages].[3PT%]) AS [AvgOf3PT%], Avg([Game Percentages].[FT%]) AS [AvgOfFT%], Avg([Game Percentages].OffReb) AS AvgOfOffReb, Avg([Game Percentages].DefReb) AS AvgOfDefReb, Avg([Game Percentages].Tot) AS AvgOfTot
FROM Teams INNER JOIN (Game INNER JOIN [Game Percentages] ON Game.GameID = [Game Percentages].GameID) ON Teams.TeamID = Game.Home
GROUP BY Teams.Team, Game.Away;

When i put it in datasheet view it gives several rows of the same team with different averages. My tables include

GAME
gameid(pk)
hometeam
awayteam
date
venueid
homeline
awayline

SCORE
gameid(pk)
periodid
score

STATS
gameid(pk)
teamid
fgm
fga
ftm
fta
3ptm
3pta
offreb
defreb
assist
steal
t/o
block
fouls

TEAM
teamid(pk)
team

VENUE
venueid(pk)
venue

PERIOD
periodid(pk)
period

i don't know how to do this because i want the away team but both home and away are linked to team.
Thanks

 

[SteveR77]

What type of field are the home team and away team fields? If they are yes/no fields you would just need to say that the away field was true.


Steve

 

[r937]

where does [Game Percentages] come from?

if that's a separate query, you might just be ablt to do what you want in one query rather than two

the trick is, you have to join GAME to TEAM twice, something like

FROM game
INNER JOIN team H
ON game.hometeam = H.teamid
INNER JOIN team A
ON game.awayteam = A.teamid

("something like" because of course Access will parenthesize the joins in the context of the entire query)


rudy

http://r937.com/

SQL Consulting

 

[phunkyb]

i already have game joined to team twice. As i'm not too confident with sql, i'm using design view and have been able to make the home team averages query successfully and would not think it would be possible to join an home and away averages query together. If joining home and away to team is all that is required, what am i doing wrong then?

 

[r937]

what are you doing wrong? no idea, because i can't see what's in [Game Percentages]

if you'd like help, i'll give it a try, please post a few sample rows of each table, as well as a few sample rows of the desired result, such that it is clear how the calculations were arrived at

rudy

http://r937.com/

SQL Consulting

 

[phunkyb]

This is what the outcome should be. This is the outcome of my home averages query.

Team AvgOfFG% AvgOf3PT% AvgOfFT% AvgOfOffReb AvgOfDefReb AvgOfTot
Victoria Giants 46.86 36.30 74.43 13.45 26.25 39.70
Townsville Crocodiles 46.5652435544365 35.22 78.6254488212333 14.85 27.15 42
Perth Wildcats 46.10 34.99 74.97 13.61 27.8333333333333 41.4444444444444
Adelaide 36ers 45.5828370343191 32.5995574090462 70.9236986651785 15.85 28.35 44.2
Hunter Pirates 45.0838275841689 33.5230332916944 74.7295779044246 13.1 29.35 42.45
------------------------------------
This is how it is currently turning out

Team AvgOfFG% AvgOf3PT% AvgOfFT% AvgOfOffReb AvgOfDefReb AvgOfTot
Adelaide 36ers 39.2647058823529 32.4587706146927 74.0740740740741 16 30.5 46.5
Adelaide 36ers 43.1006493506493 31.6770186335404 65.5228758169935 18.5 28 46.5
Adelaide 36ers 46.7226890756303 35.3302611367128 75 19.5 23.5 43
Adelaide 36ers 52.4390243902439 37.280701754386 73.7616099071207 10.5 26 36.5
-----------------------------------
Game Percentages is a query i made. The sql for this is

SELECT Stats.GameID, Teams.Team, Stats.FGM, Stats.FGA, Stats!FGM/Stats!FGA*100 AS [FG%], Stats.[3PTM], Stats.[3PTA], Stats![3PTM]/Stats![3PTA]*100 AS [3PT%], Stats.FTM, Stats.FTA, Stats!FTM/Stats!FTA*100 AS [FT%], Stats.OffReb, Stats.DefReb, Stats!OffReb+Stats!DefReb AS Tot
FROM Teams INNER JOIN Stats ON Teams.TeamID = Stats.TeamID
ORDER BY Stats.GameID;
-------------------------------
Some lines from it

GameID Team FGM FGA FG% 3PTM 3PTA 3PT% FTM FTA FT% OffReb DefReb Tot
1 New Zealand Breakers 32 76 42.1052631578947 14 34 41.1764705882353 32 41 78.0487804878049 14 24 38
1 Adelaide 36ers 38 82 46.3414634146341 8 18 44.4444444444444 27 34 79.4117647058823 16 31 47
2 Townsville Crocodiles 46 96 47.9166666666667 7 22 31.8181818181818 15 21 71.4285714285714 24 20 44
2 Cairns Taipans 44 77 57.1428571428571 7 26 26.9230769230769 23 27 85.1851851851852 13 23 36
3 Hunter Pirates 32 83 38.5542168674699 4 19 21.0526315789474 9 14 64.2857142857143 14 32 46
3 Melbourne Tigers 36 84 42.8571428571429 3 14 21.4285714285714 30 36 83.3333333333333 14 36 50

i hope this helps and thanks for your help today

 

[r937]

that info helps a lot

when you say "This is what the outcome should be" are you looking to get averages for both home and away games combined per team, or separate home and away averages per team?

also, it appears that both a team's home stats and away stats are separate lines in the STATS table, so i don't see why the GAME table is needed in the averages query, unless you need to distinguish home averages separate from away averages



rudy

http://r937.com/

SQL Consulting

 

[phunkyb]

sorry it has taken so long to reply.i'm trying to get averages for separate home and away games per team. I want to get the averages for teams when playing at home, then all games away and another query of all games combined to see the differences when the teams play at home and away.

 

[JonFer]

Try this to do both home and away in one query:

SELECT Teams.Team,
    IIf([Game Percentages].TeamID=Games.HomeTeam,"Home","Away") as HomeAway,
    Avg([Game Percentages].[FG%]) AS [AvgOfFG%], 
    Avg([Game Percentages].[3PT%]) AS [AvgOf3PT%], 
    Avg([Game Percentages].[FT%]) AS [AvgOfFT%], 
    Avg([Game Percentages].OffReb) AS AvgOfOffReb, 
    Avg([Game Percentages].DefReb) AS AvgOfDefReb, 
    Avg([Game Percentages].Tot) AS AvgOfTot
FROM Teams INNER JOIN (Game INNER JOIN [Game Percentages] ON Game.GameID = [Game Percentages].GameID) ON Teams.TeamID = [Game Percentages].TeamID
GROUP BY Teams.Team,
   IIf([Game Percentages].TeamID=Games.HomeTeam,"Home","Away");

I didn't test this so there may be syntax and spelling errors.

 

[phunkyb]

thanks jonfer. wow this sql language is quite powerful. the sql works and it works well but i would rather have two queries for home and away where all the teams are shown.
cheers mate

 

[r937]

something perhaps like this, doing away with the [Game Percentages] query, calculating the averages directly...

[tt]select Teams.Team
, 1 as HomeAwaySortKey
, 'Home' as HomeAway
, Avg(Stats!FGM/Stats!FGA*100) as [AvgOfFG%]
, Avg(Stats![3PTM]/Stats![3PTA]*100)
as [AvgOf3PT%]
, Avg(Stats!FTM/Stats!FTA*100) as [AvgOfFT%]
, Avg(Stats.OffReb) as AvgOfOffReb
, Avg(Stats.DefReb) as AvgOfDefReb
, Avg(Stats!OffReb
+Stats!DefReb) as AvgOfTot
from (
Teams
inner
join Game
on Teams.Teamid = Game.hometeam
)
inner
join Stats
on Teams.TeamID = Stats.TeamID
and Game.GameID = Stats.GameID
UNION ALL
select Teams.Team
, 2 as HomeAwaySortKey
, 'Away' as HomeAway
, Avg(Stats!FGM/Stats!FGA*100) as [AvgOfFG%]
, Avg(Stats![3PTM]/Stats![3PTA]*100)
as [AvgOf3PT%]
, Avg(Stats!FTM/Stats!FTA*100) as [AvgOfFT%]
, Avg(Stats.OffReb) as AvgOfOffReb
, Avg(Stats.DefReb) as AvgOfDefReb
, Avg(Stats!OffReb
+Stats!DefReb) as AvgOfTot
from (
Teams
inner
join Game
on Teams.Teamid = Game.awayteam
)
inner
join Stats
on Teams.TeamID = Stats.TeamID
and Game.GameID = Stats.GameID
UNION ALL
select Teams.Team
, 3 as HomeAwaySortKey
, 'Both' as HomeAway
, Avg(Stats!FGM/Stats!FGA*100) as [AvgOfFG%]
, Avg(Stats![3PTM]/Stats![3PTA]*100)
as [AvgOf3PT%]
, Avg(Stats!FTM/Stats!FTA*100) as [AvgOfFT%]
, Avg(Stats.OffReb) as AvgOfOffReb
, Avg(Stats.DefReb) as AvgOfDefReb
, Avg(Stats!OffReb
+Stats!DefReb) as AvgOfTot
from Teams
inner
join Stats
on Teams.TeamID = Stats.TeamID
order
by 1, 2[/tt]

rudy

http://r937.com/

SQL Consulting

 

[phunkyb]

i copied your sql into access and i got this message

"you tried to execute a query that does not include the specified expression 'team' as part of an aggregate function"

what does that mean?

 

[r937]

it means i forgot to put GROUP BY Team into each of the three subqueries in the union





rudy

http://r937.com/

SQL Consulting

 

[phunkyb]

sorry for asking such a dumb question but where do i put the group by team. i put it after select teams.team and tried a few other but was wrong.

 

[r937]

at the end of each subquery

i.e. before the UNION ALL for the first two, and before the ORDER BY for the third

rudy

http://r937.com/

SQL Consulting