I have a back end database with a list of players, their games played and won. When formatting this for displaying on my web page I want to order them by their percentage score (Won/Played).
However because this data is grouped before the ordering, its not sorting them correctly. I don't have anything in the table other than Name, Played, Won. All the other information I'm creating on the server.
Is there a way where I can:
1. Order by a variable thats not in the database (e.g. WinPer in the below code)
2. Order by before the Group by happend
All help much appreciated, example code attached.
sqllea = "SELECT tblPlayers.Name, tblPlayers.Player_ID, "
sqllea = sqllea & "SUM(tblResults.Played - tblResults.Won) AS Lost, "
sqllea = sqllea & "SUM(tblResults.Played) AS TotalPlayed, "
sqllea = sqllea & "SUM(tblResults.Won) AS TotalWon, "
sqllea = sqllea & "SUM(tblResults.Won / tblResults.Played *100) AS WinPer "
sqllea = sqllea & "FROM tblResults INNER JOIN tblPlayers ON tblResults.Player_ID = tblPlayers.Player_ID "
sqllea = sqllea & "GROUP BY tblResults.Player_ID, tblPlayers.Name, tblPlayers.Player_ID "
sqllea = sqllea & "ORDER BY SUM(tblResults.Won / tblResults.Played) DESC"