Two table join query 1

[drelefun]

Hi

I have two tables which i am trying to get data from. The two tables are

AFLPlayerStats( aflgame_id(pk), team_id(pk), player_id(pk), kicks, handballs. goals, behinds, hitouts, tackles)

and

AFLScore(aflgame_id, team_id(pk), quarter(pk), goals, behinds)

What I want to do is SUM(AFLPlayerStats.kicks + AFLPlayerStats.handballs) / SUM(AFLPlayerStats.goals + AFLPlayerStats.behinds) AS 'Possesions Per Score'


I thought it was a basic two table join

SELECT AFLScore.team_id, SUM(AFLPlayerStats.kicks + AFLPlayerStats.handballs) / SUM(AFLPlayerStats.goals + AFLPlayerStats.behinds) AS 'Possesions Per Score'
FROM AFLPlayerStats INNER JOIN
AFLScore ON AFLPlayerStats.aflgame_id = AFLScore.aflgame_id AND AFLPlayerStats.team_id = AFLScore.team_id
GROUP BY AFLScore.team_id

but i'm assuming there is something i need to do with the quarter and player_id fields. can anyone help?

thanks
ben

 

[SQLSister]

I'm not sure what you are trying to get,what is wrong with the result set you are currently getting? You also need to look at your calculation if the fields involved are integer fields. An interger divided by an interger will yeild an integer result which is not normally normally waht is wanted. You fix this by multiplying the bottom of the calc by 1.0.



"NOTHING is more important in a database than integrity." ESquared

 

[drelefun]

it just seems to get the wrong results and i'm not sure why.
separately the results are right

SELECT team_id, SUM(goals + behinds) AS 'Scoring Shots'
FROM AFLScore
GROUP BY team_id

and

SELECT team_id, SUM(kicks + handballs) AS 'Disposals'
FROM AFLPlayerStats
GROUP BY team_id

but the results together don't add up

 

[ethorn10]

What about:

SELECT team_id, 
    SUM(goals + behinds) AS 'Scoring Shots',
    SUM(kicks + handballs) AS 'Disposals'
FROM AFLScore INNER JOIN
      AFLPlayerStats ON AFLPlayerStats.aflgame_id=AFLScore.aflgame_id AND AFLPlayerStats.team_id=AFLScore.team_id
GROUP BY team_id

If this doesn't give the results you expect, then your initial join criteria is probably the culprit. From what I can tell, it looks like all you care about is the 'Scoring Shots' and 'Disposals' by team, not by team and game (so maybe leave out the game part of the join?).

 

[drelefun]

i tried it and it still doesn't work. i'll try some different joins and see how that goes.

 

[RyanEK]

Hi,

Your request is a little confusing. Perhaps you can provide sample data and expected results?

But from what I could gather, sounds like you could use a cross join. Give this a go...

select s.team_id,
SUM(kicks + handballs) / SUM(goals + behinds) as 'Possesions Per Score'
from AFLPlayerStats as s
cross join AFLScore as c
where s.team_id = c. team_id
group by s.team_id

Ryan

 

[drelefun]

i tried your tip ryan but it wasn't what i was after. here is some data from the table aflplayerstats

Aflgame_id Team_id Player_id kicks handballs
2 mel 58 12 6
2 mel 358 2 3
2 mel 359 5 14
2 mel 360 8 6
2 mel 362 6 12
2 mel 364 7 7
2 mel 366 4 5
2 mel 370 9 9
2 mel 372 7 14

and

aflscore

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

what i want to do is get the result is if we use the example above it will look like

Team_id Possessions per score
mel 8

as it is 136(kicks + handballs)/17(goals + behinds)

would having the quarter and player_id as pk in their respective tables affect how i need to write this query?

 

[mutley1]

Drelefun,

From data given,

136(kicks+handballs) Correct
17 (Goals+behinds) ???????

4+2+0+3+2+5+4+2 for goals = 22
3+0+2+3+7+0+5+3 for behinds = 23

Total = 45. You must have a quater clause in your original which I cant see?

 

[drelefun]

sorry i just meant really for
2 mel 1 4 3
2 mel 2 2 0
2 mel 3 0 2
2 mel 4 3 3

as its based on it being the aflgame_id = 2 and team_id = mel

 

[WittyD]

SELECT Team_id, SUM(Kicks + handballs) /
(SELECT SUM(goals + behinds) AS Expr1
FROM dbo.Aflscore AS t2
WHERE (Aflgame_id = t1.aflgame_id) AND (team_id = t1.Team_id)
GROUP BY Aflgame_id, team_id) AS Pos_Per_Score
FROM dbo.AFLPlayerStats AS t1
GROUP BY aflgame_id, Team_id

 

[Welshbird]

SQLSister - multiplying my calcs by 1.00 instead of converting the numbers is fab tip! Thanks for that.

Star for ya.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen