query calculation

leena

Hi Everyone,

I am trying to order a group of records from a select statement by the result of dividing the value of 2 different fields.

For example something like this:

SELECT ITTeamMember.PlayerID, ITTeamMember.TotalGamesPlayed, ITTeam.TeamName, ITDivisionMember.TeamID, tot=(ITTeamMember.TeamMemberStat3/ITTeamMember.TeamMemberStat7)
FROM ITDivisionMember,ITTeam, ITTeamMember, ITPlayer
WHERE ITDivisionMember.divisionID = '$divisionID'
AND ITDivisionMember.DivisionMemberSub='$subDivision'
AND ITDivisionMember.TeamID=ITTeam.TeamID
AND ITTeamMember.DivisionMemberID=ITDivisionMember.DivisionMemberID
AND ITTeamMember.TeamMemberReg=1
AND ITTeamMember.TeamMemberPlayer=1
AND ITTeamMember.TeamMemberStatus=1
AND ITPlayer.PlayerID=ITTeamMember.PlayerID
AND ITPlayer.LeagueID=ITTeamMember.LeagueID
ORDER BY tot
LIMIT 25

I am not sure how this works. I tried just putting:
ORDER BY =(ITTeamMember.TeamMemberStat3/ITTeamMember.TeamMemberStat7)

but that only seems to work with one of the records I found.

Thanks for your time. Any help is greatly appreciated.

Lars_Berg

SELECT ITTeamMember.PlayerID, ITTeamMember.TotalGamesPlayed, 
ITTeam.TeamName, ITDivisionMember.TeamID, ITTeamMember.TeamMemberStat3/ITTeamMember.TeamMemberStat7) [b] as tot [/b]
FROM ITDivisionMember,ITTeam, ITTeamMember, ITPlayer 
WHERE ITDivisionMember.divisionID = '$divisionID' 
AND ITDivisionMember.DivisionMemberSub='$subDivision' 
AND ITDivisionMember.TeamID=ITTeam.TeamID 
AND ITTeamMember.DivisionMemberID=ITDivisionMember.DivisionMemberID 
AND ITTeamMember.TeamMemberReg=1 
AND ITTeamMember.TeamMemberPlayer=1 
AND ITTeamMember.TeamMemberStatus=1 
AND ITPlayer.PlayerID=ITTeamMember.PlayerID 
AND ITPlayer.LeagueID=ITTeamMember.LeagueID 
ORDER BY tot 
LIMIT 25

leena

I tried it they way you suggested, however it seems like it is not ordering properly. It will not order by my calculated field even if I put the "ORDER BY tot" in there.

leena

It seems as though it is limiting the records to 25, and then ordering them. I want them ordered first, and then limited to the top 25.

Any ideas why this won't work?

Lars_Berg

What do you expect and what do you get?

Maybe it should be order by tot desc

leena

Yeah I tried ORDER BY tot DESC and ORDER BY tot ASC

Neither seem to look right.

Normally I will have a list of players and the field being sorted by has far more that 25 people with that field value over 0. However it will show one player where that field is 5.5, and then it will grab all the rest of the players with field values of 0.

I should never have any players with 0, because there are about 75 players with values over 0, and I only want the top 25.

This is really messed up. I will simplify my query, here is what it looks like:
SELECT TeamMemberStat3 / TeamMemberStat7 as tot
FROM ITTeamMember
WHERE LeagueID=2000
ORDER BY tot
LIMIT 25

tomhath

What happens if you add this to the WHERE clause?

AND TeamMemberStat3 > 0
AND TeamMemberStat7 > 0