Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    May 2006
    Posts
    20

    Question Unanswered: scores, players and averages

    Hi guys,

    I took computing at a-level (UK) and learned quite a bit about database normalisation. However, I have been presented with a project and am a little stuck at the best approach for this task:

    I will have many users, each playing many games, and each game recieves a score. Here's a simplified look at my tables:

    tblPlayer
    playerID
    playerName
    gamesPlayed

    tblGames
    playerID
    gameID
    score


    So, for example, player with id '1' might play 50 games, and would as such have 50 records.

    Basically, I need to first total all of the scores for each individual player, and then calculate the average score (total score / games played). I also need these averages sorted descendingly. And just to note, there could possibly be many thousands of games played!

    Is there a good way to do this just using SQL? Or do I need a different table design?

    Any help is much appreciated.
    Craig

  2. #2
    Join Date
    May 2006
    Posts
    20
    I have an additional query....

    Consider the table Results.

    Results
    player_id
    tournament_id
    player_score


    player_id & tournament_id will make the joint primary key

    what I need to be able to do is, first, select all scores from a single tournament, and order them descending. I then need to be able to give the highest score a "position" of "1", the second "2", and to return that value AS position.

    I think I've seen it done before, but can't remember how.

    Many thanks!

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select playerID
         , sum(score)/count(*) as avg_score_1
         , avg(score)          as avg_score_2
      from tblGames
    group
        by playerID  
    order
        by avg_score_1 desc
    two different ways to compute the average score -- do you think they will be the same or different?

    Code:
    select player_id
         , player_score
         , ( select count(player_score)+1 
               from Results
              where tournament_id = R.tournament_id
                and player_score > R.player_score ) as position
      from Results R
     where tournament_id = 937  
    order
        by position
    can you figure out why i add 1 to the count?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2006
    Posts
    20
    Thanks for that I will try it out later on and let you know how I got on.

  5. #5
    Join Date
    May 2006
    Posts
    20
    Also, I am worried about performance issues. When we have, say, 100,000 records in the games played table, should I expect any performance issues? If so, is there any way I can combat this?

    Thanks!

    Craig

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, expect some

    yes, declare an index on Results.player_score

    better yet, try an index on (tournament_id,player_score)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2006
    Posts
    20
    Is there a way I could create an index of "averages" ? I have heard someone talk about a stored procedure? Unfortunately I am unsure of what they are talking about.

    Thanks for the speedy reply r937 Much appreciated

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what do you mean by "an index of averages"

    stored procedure isn't gonna help here too much
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2006
    Posts
    20
    Im not entirely sure what I mean.

    Basically, my thinking is, every time that avg(score) is executed, the averages are re-calculated. I am just wondering if this could be made more efficient, without storing unnecesarry data.

    Thanks

  10. #10
    Join Date
    May 2006
    Posts
    20
    Code:
    select player_id
         , player_score
         , ( select count(player_score)+1 
               from Results
              where tournament_id = R.tournament_id
                and player_score > R.player_score ) as position
      from Results R
     where tournament_id = 937  
    order
        by position
    A quick question about this. How can I select records only where the position equals 1st, or 3rd, for instance.

    Also, is it possible to count the number of records where position = 1 ?

    Thanks

  11. #11
    Join Date
    May 2006
    Posts
    20
    Basically, what I need (would like) is a result set like this:

    memberID || position1st || position2nd || position3rd

    position1st is the number of times that player has come 1st in a tournament.

    Tricky :s

    Cheers

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select player_id
         , sum(case when position=1 
                    then 1 else 0 end) as position1st
         , sum(case when position=2 
                    then 1 else 0 end) as position2nd
         , sum(case when position=3 
                    then 1 else 0 end) as position3rd
      from (
           select player_id
                , player_score
                , ( select count(player_score)+1 
                      from Results
                     where tournament_id = R.tournament_id
                       and player_score > R.player_score ) as position
             from Results R
            where tournament_id = 937  
           ) as d
    group
        by player_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    May 2006
    Posts
    20
    You legend That's solved my problem outright

    I'd love to know how the ranking part works, as I'm not sure about the maths of that bit.

    Thanks very much pal, Really appreciate it


  14. #14
    Join Date
    May 2006
    Posts
    20
    I'm just wondering whether I am actually designing the database structure correctly.

    To recap the tables I have:

    Tournament
    id
    venue_id
    date

    Results
    tournment_id
    member_id
    scpre

    Member
    id
    poker_name

    bonus
    id
    value
    description

    BonusResults
    tournament_id
    member_id
    bonus_id


    Obviously I have the score, but then I need to add the bonus values onto that score, which exist in the bonusresults table. From There, I then need the positions.

    As I say, is this design correct? Or should I just store the "final score" somewhere :s

    Many thanks
    Craig

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    look at the primary key for the Results table

    now look at the primary key for the BonusResults table

    shouldn't those tables be the same single table with 2 data columns?

    the only reason i can think of why they couldn't is that maybe a single player can have more than one bonus per tournament
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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