Still having some questions about statistics

1. Junior Member
Join Date
Dec 1969
Posts
7

## Still having some questions about statistics

I am getting there, but have a problem i can&#039;t resolve yet.<BR>My database looks like this:<BR>table colums contain: Player, course, round, score, and a number of details for that round, a total of 25 columns<BR>then each row contains a new round for each player.<BR>There are 15 Tours of 2 rounds, and 4 Tours of 4 rounds, we have 25 players, so i&#039;ll probably end up with 1100 records :)<BR>My problem is this: I want to display the 2 (or 4) rounds of each Tour in one sheet, AND want to rank the players at the same time by avg score, but the players that only played 1 round have to be ranked lower than the players that played 2 rounds, it gets even trickier when a Tour has 4 rounds.... any1 having an idea?

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## If you end up with...

...1100 records, then several people didn&#039;t play all their rounds.<BR><BR>My math says<BR>( (15 x 2) + (4 x 4) ) x 25 <BR>is <BR>( 30 + 16 ) x 25<BR>is<BR>46 x 25<BR>is <BR>1150<BR><BR>[I did it by saying "25 is a quarter of 100, 46 x 100 is 4600, a quarter of 4600 is half of half of 4600, or half of 2300, which is 1150." Your mileage may vary.]<BR><BR>ANYWAY...<BR><BR>Let&#039;s see if we can do this all in one query...<BR><BR>First question: How many points are possible in one round per player? Let me assume the number is less that a million. If so:<BR><BR>SELECT ( Count(*) * 1000000.0 ) + Avg(score) AS Rank,<BR> &nbsp; &nbsp; Avg(score) AS PlayerAvg, <BR> &nbsp; &nbsp; Player<BR>FROM Rounds<BR>WHERE tourNumber = 3 <BR>GROUP BY Player<BR>ORDER BY 0 Desc<BR><BR>That doesn&#039;t show the scores for the individual rounds. It only gets the rankings and averages for all players for one Tour. [You didn&#039;t mention a "tourNumber" field in your posting, but I assume it must be there?]<BR><BR>See the trick in generating the rankings? A person who only plays 1 round will have Count(*) of 1, so his rank will be 1000000 plus his average. Somebody who has played two rounds will have a rank of 2000000 plus his average. Etc. So all those who finished all rounds will always be ranked ahead of those who haven&#039;t.<BR><BR>NOW...<BR><BR>*IF* you *also* need all the per-round details on a per-player basis in the same report, that gets trickier. I haven&#039;t figured out a way to do that without a temp table, yet. Though a temp table isn&#039;t a horrible solution, I suppose. Okay...with a temp table:<BR><BR>Just change the above query so it does "SELECT ... INTO TEMP" and then do<BR><BR>SELECT * FROM Rounds, Temp<BR>WHERE Rounds.Player = Temp.Player<BR>AND Rounds.tourNumber = 3<BR>ORDER BY Temp.Rank, Rounds.round<BR><BR>Untested, but it looks to me like it would work.<BR><BR>

3. Junior Member
Join Date
Dec 1969
Posts
7

## RE: If you end up with...

yes you are right, i have a field with "tournumber", actually it is the name of the course that is played. And yes, not all members play their rounds. I had a lil trouble with the ranking because the lower the avg, the better the rank, but I solved it like this:<BR>SELECT (COUNT(*) * -10000.0) + AVG(H18) AS Rank, <BR>AVG(H18) AS Average,name <BR>FROM rounds WHERE H18 &#062; 0 AND course = &#039;Kapalua Plantation 2000&#039; GROUP BY name <BR>ORDER BY (COUNT(*) * -10000.0) + AVG(H18)<BR><BR>the ORDER BY 0 you suggested doesn&#039;t work on our server, it seems invalid<BR><BR>by the way scores are ranged between 55 and 90, lowest score is best. (GolfTourney)<BR><BR>Anyway, thank you very much for your suggestion, I can go on with my sheets again :))<BR>THANKS JAN <BR> <BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

Should have been <BR>&nbsp; &nbsp; ORDER BY 1<BR><BR>The SELECTed fields are numbered, starting at 1, in SQL. So that ORDER BY 1 is equivalent to the ORDER BY you actually used.<BR><BR>RecordSet fields are, of course, numbered from zero and I just slipped a mental cog for a moment there.<BR><BR>I like your solution for the ranking!<BR><BR>I probably would have done something like:<BR><BR>( ( 10 - COUNT(*) ) * 10000 ) + AVG(H18) <BR><BR>but yours works as well and is shorter.<BR><BR>How come you need the "WHERE H18 &#062; 0" in that query??? Why would you put in a record, at all, if there is no score?<BR><BR>Ummm...just realized...H18 is probably "Handicapped 18 hole score", yes? And you probably also have H9 or some such? <BR><BR>Anyway, cute solution, I hope you agree! Sometimes math works where complicated logic doesn&#039;t.<BR><BR>

5. Junior Member
Join Date
Dec 1969
Posts
7

## RE: Sorry about the typo!

You guessed right again :) The members have the possibility to play only 9 holes at once and submit their score and stats, then play the other 9 holes later. To avoid getting those scores in the rankings, i leave the H18 field valued "zero" untill the entire 18 holes are played, and half finished rounds will not be displayed by stating "WHERE H18 &#062; 0"<BR><BR>In the *ReportForm* I also use the input to avoid double posts by this code:<BR>&#060;%<BR>&#039;check if a part or the whole round already has been played<BR><BR>set Grs=Server.CreateObject("adodb.Connection")<BR>Gst rDS = "DRIVER={Microsoft Access Driver (*.mdb)};"&"DBQ=" &"\BBzServerpga.mdb"&";"<BR>Grs.open(GstrDs)<BR>Gs qlstmt = "SELECT * from rounds WHERE (name= &#039;"& sName &"&#039; AND course= &#039;"& course &"&#039; AND round= &#039;"& bround &"&#039;)"<BR>set GTs = Grs.execute(Gsqlstmt)<BR><BR>If GTs.eof then<BR> rnds= 0<BR> rndsF9= 0<BR> rndsB9= 0<BR> rnds18= 0<BR>Else<BR> F9Score = GTs("F9")<BR> If F9Score &#062; 0 THEN<BR> rndsF9 = 1<BR> End If<BR> B9Score = GTs("B9")<BR> If B9Score &#062; 0 THEN<BR> rndsB9 = 2<BR> End If<BR> Score18 = GTs("H18")<BR> If Score18 &#062; 0 THEN<BR> rnds18 = 6<BR> End If<BR>End If<BR><BR>rnds= rndsF9 + rndsB9 + rnds18<BR><BR>GTs.close<BR>set GTs=nothing<BR>%&#062;<BR><BR><BR><BR> &#060;table&#062;<BR> &#060;tr&#062;<BR> &#060;td&#062;Enter Score&#060;/td&#062;<BR> &#060;/tr&#062;<BR> &#060;tr&#062;<BR> &#060;td&#062;Front 9&#060;/td&#062;<BR> &#060;td&#062;<BR> &#060;%<BR> If rndsF9 = 1 Then<BR> response.write"You posted a Front 9 score of&nbsp; "& F9Score &"&nbsp; already"<BR> response.write"&#060;input type=&#039;HIDDEN&#039; name=&#039;F9Score&#039; value="& F9Score &"&#062;"<BR> ElseIf rnds &#062; 5 Then<BR> response.write"&nbsp;"<BR> response.write"&#060;input type=&#039;HIDDEN&#039; name=&#039;F9Score&#039; value="& F9Score &"&#062;"<BR> Else<BR> %&#062;<BR> Â* &#060;select name="F9Score"&#062;<BR> &#060;option selected value=0&#062;Front 9 Score&#060;/option&#062;<BR>&#060;% <BR>For x=20 to 63<BR> response.write "&#060;option value=" & x & "&#062;" & x & "&#060;/option&#062;"<BR>next<BR>%&#062;<BR> &#060;/select&#062;<BR>&#060;% End if %&#062;<BR><BR> &#060;/td&#062;<BR> &#060;/tr&#062;<BR> &#060;tr&#062;<BR> &#060;td&#062;Or Back 9&#060;/td&#062;<BR> &#060;td&#062;<BR> &#060;%<BR> If rndsB9 = 2 Then<BR> response.write"You posted a Back 9 score of&nbsp; "& B9Score &"&nbsp; already"<BR> response.write"&#060;input type=&#039;HIDDEN&#039; name=&#039;B9Score&#039; value="& B9Score &"&#062;"<BR> ElseIf rnds &#062; 5 Then<BR> response.write"&nbsp;"<BR> response.write"&#060;input type=&#039;HIDDEN&#039; name=&#039;B9Score&#039; value="& B9Score &"&#062;"<BR> Else<BR> %&#062;<BR> &#060;select name="B9Score"&#062;<BR> &#060;option selected value=0&#062;Back 9 Score&#060;/option&#062;<BR>&#060;% <BR>For x=20 to 63<BR> response.write "&#060;option value=" & x & "&#062;" & x & "&#060;/option&#062;"<BR>next<BR>%&#062;<BR> &#060;/select&#062;<BR> &#060;% End if %&#062;<BR><BR> &#060;/td&#062;<BR> &#060;/tr&#062;<BR> &#060;tr&#062;<BR> &#060;td&#062;Or 18 holes&#060;/td&#062;<BR> &#060;td&#062;<BR> &#060;%<BR> If (rndsF9 = 1) OR (rndsB9) = 2 Then<BR> response.write"&nbsp;"<BR> response.write"&#060;input type=&#039;HIDDEN&#039; name=&#039;Score18&#039; value="& Score18 &"&#062;"<BR> ElseIf rnds &#062; 5 Then<BR> response.write"You already played this round, if there was a mistake, contact Dz or ThunderHawk"<BR> response.write"&#060;input type=&#039;HIDDEN&#039; name=&#039;Score18&#039; value="& Score18 &"&#062;"<BR> Else<BR> %&#062;<BR> &#060;select name="Score18"&#062;<BR> &#060;option selected value=0&#062;18 Holes Score&#060;/option&#062;<BR>&#060;% <BR>For x=50 to 99<BR> response.write "&#060;option value=" & x & "&#062;" & x & "&#060;/option&#062;"<BR>next<BR>%&#062;<BR> &#060;/select&#062;<BR> &#060;% End if %&#062;<BR> &#060;/td&#062;<BR> &#060;/tr&#062;<BR> &#060;tr&#062;<BR> &#060;td&#062;Â* &#060;input type="submit" value="Send"&#062;<BR> &#060;input type="reset" value="Reset" onclick=history.back()&#062;&#060;/td&#062;<BR> &#060;/tr&#062;<BR> &#060;/table&#062;<BR><BR><BR> &#060;input type="HIDDEN" name="sName" value="&#060;%= sName %&#062;"&#062;<BR> &#060;input type="HIDDEN" name="course" value="&#060;%= course %&#062;"&#062;<BR> &#060;input type="HIDDEN" name="bround" value="&#060;%= bround %&#062;"&#062;<BR> &#060;input type="HIDDEN" name="cPar" value="&#060;%= cPar %&#062;"&#062; <BR>&#060;/form&#062;

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•