SQL ORDER BY Before GROUP BY

Hi,

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.

Thanks.
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"

Open in new window

XtreamXTCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sonicefuCommented:

sqllea = "SELECT * from ( "
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 ) as Tbl order by WinPer"

Open in new window

0
sonicefuCommented:

sqllea = "SELECT * from ( "
sqllea = 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 ) as Tbl order by WinPer"

Open in new window

0
XtreamXTCAuthor Commented:
Thanks for the quick reply, although it still seems to be grouping before the ordering. Here is the page on the site so you can see what is happening:

http://www.pool.drunkensailor.co.uk/index.asp?pageid=1

the WinPer variable is what is used on the right hand side and what I want to Order By, as you can see its still not ordering correctly :(
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

ee_rleeCommented:
try this
sqllea = "SELECT TotalWon/TotalPlayed * 100 as WinPer FROM "
sqllea = 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 & "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 TotalWon/TotalPlayed DESC"

Open in new window

0
Jinesh KamdarCommented:
Hey, ur ORDER BY clause is incorrect. Try this.
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) * 100) DESC"

Open in new window

0
YiogiCommented:
Tried this?

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 6  DESC"
0
Jinesh KamdarCommented:
Scratch that, silly me :-|
0
XtreamXTCAuthor Commented:
Thanks guys, but still no joy :(

ee_rlee - It doesn't like the syntax, also TotalWon and TotalPlayed are not within the database, they are created server side.

Viogi - Sorry mate, still does the same thing.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
here we go:
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.0) 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( CAST(tblResults.Won as decimal(20,4)) / tblResults.Played * 100.0) DESC"

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ps: how do you display the data in the grid? I assume that you have maybe a grid/dataview that does a sorting on Pld desc, overriding the SQL's order by.
run the query directly, and you will see that it will order by correctly
0
XtreamXTCAuthor Commented:
Hi Angelll,

I'm getting an error code "error '80004005' " with the above.

Its being displayed on a web page using ASP, nothing in there is overriding the SQL sort, so its just moving through each record after the SQL sortation.

The sortation is working to an extent, in that I know its using the SQL, I tried AVG and that seemed to work better, but was still not getting it right, because there is more than 1 record for each player.
0
YiogiCommented:
When you run the query in a query analyzer or if its SQL 2005 in management studio do the results show correctly? I too think there it must be something overwriting the SQL default sort. That should work. Please let us know if you get the same messed up result running the query directly in the SQL Server and not in your control.

Thanks.
0
ee_rleeCommented:
can you this? TotalWon and TotalPlayed should work because it is a field from a new table, t.
sqllea = "SELECT (TotalWon/TotalPlayed * 100) as WinPer FROM "
sqllea = 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 & "FROM tblResults INNER JOIN tblPlayers ON tblResults.Player_ID = tblPlayers.Player_ID "
sqllea = sqllea & "GROUP BY tblResults.Player_ID, tblPlayers.Name, tblPlayers.Player_ID) t"
sqllea = sqllea & "ORDER BY (TotalWon/TotalPlayed) DESC"

Open in new window

0
XtreamXTCAuthor Commented:
Hi Guys,

Thanks for all the support, I've attached the whole ASP code used in the web page, Its running from a back end Access Database, so I'm not using SQL Server, as I don't have it installed. :(

As you can see from the code, nothing is overriding the sort, and it works fine when sorting on other criteria, e.g. played or won. I think its trying to sort by the 1st record for each player, rather than whole of the won/played for each player.

Sorry if i've not been very discriptive, very much a newb at SQL.


Code.txt
0
ee_rleeCommented:
hi, the problem relies here:
SUM(tblResults.Won / tblResults.Played *100.0) AS WinPer

this is not the correct way to compute for the winning percentage. it should be
(SUM(tblResults.Won)/SUM(tblResults.Played) * 100.0) AS WinPer

please try my prev post or try this one.
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) / SUM(tblResults.Played) * 100.0) 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) / SUM(tblResults.Played) * 100.0) DESC"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
XtreamXTCAuthor Commented:
Thanks ee_rlee that works a treat, all the time I was looking at it from the wrong angle!

Thanks to all those who helped!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.