[RESOLVED] Equation in SQL

TheIrishThug

This query sums the score of all the points a player gets for each tournament and then produces the list.

The SQL works in Access, but doesn't work when I move it to my website using php and mysql.

SELECT PlayerList.PlayerName,
Sum((SQR((TourneyList.PrizePool/TourneyList.Entrants)/250+1)*log((TourneyList.PrizePool/TourneyList.Entrants)/250+1))*
(log(TourneyList.Entrants/250+1))*
((5*CashList.Cash/TourneyList.PrizePool)+(1-1/TourneyList.PlacesPaid))) AS Score
FROM (CashList INNER JOIN TourneyList ON CashList.TourneyID = TourneyList.TourneyID) 
INNER JOIN PlayerList ON CashList.PlayerID = PlayerList.PlayerID
WHERE Year(TourneyList.EventEndDate)='2006' GROUP BY PlayerList.PlayerName
ORDER BY
Sum((SQR((TourneyList.PrizePool/TourneyList.Entrants)/250+1)*log((TourneyList.PrizePool/TourneyList.Entrants)/250+1))*
(log(TourneyList.Entrants/250+1))*
((5*CashList.Cash/TourneyList.PrizePool)+(1-1/TourneyList.PlacesPaid)))
DESC;

mjax

Some functions you use have other names in MySQL:

SQR() => SQRT()
LOG() => LOG10()

TheIrishThug

Thanks for that, but now I am getting an error message saying that I'm not using a GROUP BY function correctly.
Another quick question, since in the Select I am labeling the equation as Score, can I say "ORDER BY Score"?

SELECT PlayerList.PlayerName,PlayerList.PlayerID, 
Sum((SQRT((TourneyList.PrizePool/TourneyList.Entrants)/250+1)*LOG10((TourneyList.PrizePool/TourneyList.Entrants)/250+1))*
(LOG10(TourneyList.Entrants/250+1))*
((5*CashList.Cash/TourneyList.PrizePool)+(1-1/TourneyList.PlacesPaid))) AS Score 
FROM (CashList INNER JOIN TourneyList ON CashList.TourneyID = TourneyList.TourneyID) 
INNER JOIN PlayerList ON CashList.PlayerID = PlayerList.PlayerID 
WHERE Year(TourneyList.EventEndDate)='2006' 
GROUP BY PlayerList.PlayerID 
ORDER BY
Sum((SQRT((TourneyList.PrizePool/TourneyList.Entrants)/250+1)*LOG10((TourneyList.PrizePool/TourneyList.Entrants)/250+1))*
(LOG10(TourneyList.Entrants/250+1))*
((5*CashList.Cash/TourneyList.PrizePool)+(1-1/TourneyList.PlacesPaid))) 
DESC;

jheitz

Because you have more than 1 field apart from the aggregate, you need to group by all of them. So in your case:

GROUP BY PlayerList.PlayerName,PlayerList.PlayerID

Should do the trick.

About using "ORDER BY Score" give it a go. I believe ORACLE allows this whereas SQLServer does not... not sure about MySQL but the easiest thing is for you to give it a go

TheIrishThug

Thanks so much, It's working now. Looks like it was the "ORDER BY Score" that was the issue, despite the error saying it was with the GROUP BY.

bradgrafelman

Don't forget to mark this thread resolved (if it is).