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

1. Registered User
Join Date
Jul 2016
Posts
12

## 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 11:26. Reason: Pointed out should be columns not rows

## "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"

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

4. Registered User
Join Date
Jul 2016
Posts
12
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.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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?

6. Registered User
Join Date
Jul 2016
Posts
12

## 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))