AVG() or something

[TaylorLee]

Hi, this may be more of a php forum thing but I will try here first. For the record, I am new to php/mysql. I have a table "Players" that hold info on a group of golfers. For example a record would be like this: 1(id), John, Doe, 75, 72, 70, 78, 78, 75. The six numbers are golf scores that I would like to find an average also store it back into the database. The problem is that I can only seem to average a column. Not only that I wonder if I will have problems because I have data in my row that I do not want to be averaged, the players names. So, my question is how might I go about finding this average? Any help will be greatly appreciated. Thanks in advance.

Taylor 

[TaylorLee]

BTW, there won't ever be more that 6 scores to average. As a matter of fact, I am currently trying to find a way to erase the oldest score when a new score is entered. Any light that can be shined on this will be a big help as well.

[mikosiko]

Your biggest problem here is the design of your table,,, a better solution is to have 2 different tables: Players and Scores

In that way you will have a better design and your queries are going to be much more easy and you will not limited to just 6 scores by player. The other thing that you should consider is to not store in the db fields that are calculated (dependent fields),,, you can always calculate whatever you want upon retrieval.

 

[TaylorLee]

Good advice! . When I get my new data base "scores" how will I find the average? Isn't AVG() only good for columns not rows. Also, how will I link the average score with golfer associated with it.  I have read up on mysql joins but can't seem to make sense of it yet. If joins is the answer guess it's time to start reading some more! lol

[mikosiko]

..... If joins is the answer guess it's time to start reading some more! lol

 

yes... you must read a little more... just a small example to help you start:

 

Table Players

      id,                    // this is going to be the PK (primary key) and should be an auto-increment field

      firstname,

      lastname,

      // etc..etc.etc  any other column that you want to incorporate

 

Table Scores

      id,                    // this is going to be the PK (primary key) and should be an auto-increment field

      player_id,        // eventually you can choose to use this field as the PK instead of id, otherwise it must be a Unique Index.

                            // also it must be defined as a FK (Foreign Key) to the table Players

      score_date,

      course_id,      // this could be a FK to a table Courses in case that you use the index/slope of each course to calculate handicap

      score              // the field that you want to average

      <more fields... as per example #puts, GIR, etc..etc>

 

now to calculate the score average for one player (or for all of them) you simply can do:

SELECT players.id, AVG(scores.score)
  FROM players
  LEFT JOIN scores ON players.id = scores.player_id   // or scores.id if you choose to have only one id in the table scores
WHERE players.id = $playerid        // being $playerid a parameter identifying an specific player... if you want all of them do not use the
                                     // WHERE clause
GROUP BY players.id

     

[TaylorLee]

Thank you! What a help. I thought it might be something like that but when you are wandering in the dark its hard to pick a definate route.

Thanks again,

Taylor

[DavidAM]

Table Scores

      id,                    // this is going to be the PK (primary key) and should be an auto-increment field

      player_id,        // eventually you can choose to use this field as the PK instead of id, otherwise it must be a Unique Index.

                            // also it must be defined as a FK (Foreign Key) to the table Players

      score_date,

      course_id,      // this could be a FK to a table Courses in case that you use the index/slope of each course to calculate handicap

      score              // the field that you want to average

      <more fields... as per example #puts, GIR, etc..etc>

 

Just for the record, player_id can NOT be the PK and can NOT be a Unique Index in the Scores table. There will be multiple scores for any given player. This table could use a composite primary key (player_id and score_date perhaps -- or add the course_id as well) but I usually stick with the auto_increment id.

[mikosiko]

DavidM ...you'r right...  that is what happens when I answer post before have my daily cup of coffee... so Taylor scratch the player_id PK, Unique key from my comments and stick with the id as the PK with auto-increment...

 

thanks DavidM for that catch.

[TaylorLee]

Thanks for the catch. I am working on it now. Can't wait until I'm efficient at this, lol.