Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2016
    Posts
    4
    Provided Answers: 1

    Answered: Calculating the Average of Scores across a variable number of rows.

    Hi fellow developers,
    Sorry above should read variable number of columns

    The problem I have is that I have a table containing Golfers names and there respective scores for each round in a Tournament. Bear in mind that not all players score in every round due to cutoffs etc. and therefore there will be some NULL entries.
    My objective is to add a field to the table at the end that will contain the Average Score for each golfer in the Tournament.
    I can add across the columns ok but then tried do a COUNT but just couldn't get it to work. (The idea being I would calc average the long way round).

    Any solution for this would be very much appreciated.

    Name Round1 Round2 Round3 Round4 Round5 Round6 Average Score
    Player 1 87 81 xx xx Null Null
    Player 2 xx xx xx xx xx xx
    Player 3 xx xx xx xx xx Null
    And so on
    Last edited by wscwt01; 08-10-16 at 10:26. Reason: Pointed out should be columns not rows

  2. Best Answer
    Posted by wscwt01

    "Hi there,
    The upstream maths is too complicated to describe here but I have solved it....

    ((IfNull(Round1,0))+(IfNull(Round2,0))+(IfNull(Rou nd3,0))+(IfNull(Round4,0))+(IfNull(Round5,0))+(IfN ull(Round6,0)))/(((Round1 Is Not Null)+(Round2 Is Not Null)+(Round3 Is Not Null)+(Round4 Is Not Null))+(Round5 Is Not Null))+(Round6 Is Not Null))

    Thanks for your interest"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im pretty certain that the AVG function only works on neumeric values, IE a NULL values is excluded from the set of rersults used to calculate the average
    ...but check it for yourself.....

    of course what you coudl do is design the tables properly using the concepts of normalisation
    whenever you see a column called round1,round2....roundx its a nearly certain sign of flaky design

    the scores table probably should be
    player_id
    played_on datetime
    score integer

    if you wanted to knock yourself out, extend that
    the scores table probably should be
    player_id
    course_id (fk to courses)
    played_on datetime
    score integer

    table courses
    id
    title

    note yourchoice of name for your current design may cause conflicts as name is a reserved word in mysql

    https://dev.mysql.com/doc/refman/5.5/en/keywords.html
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Jul 2016
    Posts
    4
    Provided Answers: 1
    Thanks for your reply.
    I get what you are saying but I have to record the scores round by round as there is some heavy maths going on downstream! However I am still left with the problem of calculating the Averages for each player.

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok
    Im out, the pathway you are going down is in my books wrong...

    ...out of curiosity what heavy maths is going on downstream that requires a compromised non-normalised design?
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Jul 2016
    Posts
    4
    Provided Answers: 1

    Solved

    Hi there,
    The upstream maths is too complicated to describe here but I have solved it....

    ((IfNull(Round1,0))+(IfNull(Round2,0))+(IfNull(Rou nd3,0))+(IfNull(Round4,0))+(IfNull(Round5,0))+(IfN ull(Round6,0)))/(((Round1 Is Not Null)+(Round2 Is Not Null)+(Round3 Is Not Null)+(Round4 Is Not Null))+(Round5 Is Not Null))+(Round6 Is Not Null))

    Thanks for your interest

Tags for this Thread

Posting Permissions

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