asked
Created a simple table that adds up points scored per player per game, however I don't know what to do if a player didn't play in one of the games. Null seems to add a 0 value, which renders my calculations incorrect. Help, please.
Created a simple table that adds up points scored per player per game, however I don't know what to do if a player didn't play in one of the games. Null seems to add a 0 value, which renders my calculations incorrect. Help, please. I have added each column (GM1_Points, GM2_Points, etc.) to come up with the total amount scored for said record or player in the table and then divided that amount by the number of games played. However when creating the function, UPDATE Season_Points SET PPG = (GM1_Points + GM2_Points + GM3_Points + GM4_Points + GM5_Points + GM6_Points + GM7_Points + GM8_Points + GM9_Points) / 3; I am unable to address the situation that arises when a player does not play a particular game, as I am only able to apply the mathematical function to the entire PPG column, but not each unique row or record. I would like to use some value in a record column to either ignore or make null, but not convert the null value to 0 once the function is processed. I am not even sure if I can make a null value for a specific record or row column as opposed to making an entire column null. Any help would be greatly appreciated.
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
Sure. Now keep in mind that this table is not normalized, so I apologize for the remedial database structure. However, in this particular scenario, I would just like to know how to address handling players who may have not played in a game due to injury or had a BYE week. But still compute their average points per game. Thanks for your help. I greatly appreciate it. I am new to this. Player Name Team Name GM1_Points GM2_Points GM3_Points GM4_Points GM5_Points PPG Dell Favors DNP 10 - 15 2 0 Calculation (GM1_Points + GM2_Points + GM3_Points + GM4_Points + GM5_Points/5) Ron Eilliott DNP 10 0 15 2 0 Calculation (GM1_Points + GM2_Points + GM3_Points + GM4_Points + GM5_Points/5) In the example below, this player (Dell Favors) did not play in Game 2, so the data shouldn't be used to calculate his average. However, Player 2 (Ron Eilliott)played in Game 2, he just didn't score any points that game, so his game point total would be used to calculate his average. Can you create unique functions for each row as you can for a specific column? I am able to use the Calculation (GM1_Points + GM2_Points + GM3_Points + GM4_Points + GM5_Points/5) calculation to insert into the PPG column however if each record has, as in the example above details, a different number of games that need to be used in the calculation, the column calculation is not accurate. Dell Favors has played in 4 games but Ron Eilliott has played in 5 games, therefore the formula needs to account for this.
Columns Player Name Team Name GM1_Points GM2_Points GM3_Points GM4_Points GM5_Points PPG (GM1_Points + GM2_Points + GM3_Points + GM4_Points + GM5_Points/5) INSERT INTO table_name (Player Name, Team Name, GM1_Points, GM2_Points, GM3_Points, GM4_Points, GM5_Points, PPG) VALUES (Dell Favors, DNP, 10, -, 15, 2, 0); INSERT INTO table_name (Player Name, Team Name, GM1_Points, GM2_Points, GM3_Points, GM4_Points, GM5_Points, PPG) VALUES (Ron Eilliott, DNP, 10, 0, 15, 2, 0);
Try this: declare @points table (Name varchar(100), GM1 int, GM2 int, GM3 int, GM4 int, GM5 int) insert into @points select 'Dell Favors',10, null, 15, 2, 0 insert into @points select 'Ron Elliot',10, 0, 15, 2, 0 select Name, GM1, GM2, GM3, GM4, GM5, isnull(GM1,0)+isnull(GM2,0)+isnull(GM3,0)+isnull(GM4,0)+isnull(GM5,0) as TotalPoints, (select count(Points) from (values (GM1),(GM2),(GM3),(GM4),(GM5)) as Game(Points) ) as GamesPlayed, (isnull(GM1,0)+isnull(GM2,0)+isnull(GM3,0)+isnull(GM4,0)+isnull(GM5,0) ) * 1.0 / (select count(Points) from (values (GM1),(GM2),(GM3),(GM4),(GM5)) as Game(Points) ) as PPG from @points I use isnull() around each points score to prevent null values from making the total null, and then pivot the scores around into rows from columns so that I can count how many there are. COUNT by default will only count non-null values, so in this case it doesn't count the games where no score exists. This results in Name GM1 GM2 GM3 GM4 GM5 TotalPoints GamesPlayed PPG -------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- --------------- Dell Favors 10 NULL 15 2 0 27 4 6.750000000000 Ron Elliot 10 0 15 2 0 27 5 5.400000000000
This is genius. Thanks so much. A couple of questions for clarity if you wouldn't mind. 1) In line 15, what does the (Points) reference? Is this the actual creation of an Points column that is then renamed Total Points? Same question would apply to "Game". Where and how is that declared here, just so I can better understand. Ex. Game(Points)
Meant to say... 1) In line 15, what does the (Points) reference? Is this the actual creation of an Points column that is then renamed **GamesPlayed**?
I should have made it clear that I am attempting to do this in phpmyadmin so it appears the syntax in the comment reply above is not via phpmyadmin. I am at a loss here.
Ah you want a MySQL answer. Sorry I'm not sure how MySQL handles this kind of syntax. I've re-tagged the question for you
15 People are following this question.
