scores, players and averages

camason · #1 (**permalink**) 05-22-06, 18:38

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

camason · #2 (**permalink**) 05-23-06, 07:31

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!

r937 · #3 (**permalink**) 05-23-06, 08:45

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?

camason · #4 (**permalink**) 05-23-06, 08:51

Thanks for that

I will try it out later on and let you know how I got on.

camason · #5 (**permalink**) 05-23-06, 09:13

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

r937 · #6 (**permalink**) 05-23-06, 09:18

yes, expect some

yes, declare an index on Results.player_score

better yet, try an index on (tournament_id,player_score)

camason · #7 (**permalink**) 05-23-06, 09:23

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

r937 · #8 (**permalink**) 05-23-06, 09:25

what do you mean by "an index of averages"

stored procedure isn't gonna help here too much

camason · #9 (**permalink**) 05-23-06, 09:53

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

camason · #10 (**permalink**) 05-24-06, 16:50

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

camason · #11 (**permalink**) 05-24-06, 17:39

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

r937 · #12 (**permalink**) 05-24-06, 17:45

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

camason · #13 (**permalink**) 05-24-06, 17:54

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

camason · #14 (**permalink**) 05-25-06, 09:42

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

r937 · #15 (**permalink**) 05-25-06, 10:17

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

Internet Services

Web Development

Digital Marketing

Consumer Tech