I am a new member and am seeking help on a problem. Mostly the problem is me because I don't know enough, but I am plugging away a trying to figure this out.
SELECT avg(p1) FROM `rounds` WHERE `g1`= -1;
I figured out that this line of code gives me the information I want. The problem is I have to get the info from 18 of these lines.
I tried
SELECT avg(p1) FROM `rounds` WHERE `g1`= -1;
SELECT avg(p2) FROM `rounds` WHERE `g2`= -1;
SELECT avg(p3) FROM `rounds` WHERE `g3`= -1;
SELECT avg(p4) FROM `rounds` WHERE `g4`= -1;
but all I get is the last one and not the total of all of them.
What is the best way to go about solving this.
I did this with PHPMyAdmin and will eventually need to put into my PHP scripts.
Thanks for any possible help. I can explain more if I haven't done a good job.
ofWES
Getting the average from multiple fields?
Moderator: General Moderators
Thank you for your question.
My table is a record of scores from a game. There are 83 fields in it. Each segment of the game has 4 parts. There are 18 segments.
For each of the 18 segments there is an f, g, p and s, numbered for the segment. (f1, g1, p1,s1).
I want to cull a statistic from the table. In order for p to be valid, there must be a value to g. Essentially, g is a score but it is also true or false.
So;
SELECT avg(p1) FROM `rounds` WHERE `g1`= -1; gives me the average of the p for that segment when there is a g to validate it.
I have figured out that UNION will give me all the averages for the p's that are valid but I need to average those averages to make it the stat I want for that user ID.
I did run into a problem with the UNION command. When I put all 18 segments into the structure
(SELECT avg(p1) FROM `rounds` WHERE `g1`= -1)Union
(SELECT avg(p2) FROM `rounds` WHERE `g2`= -1)Union etc.....
I only got 16 rows returned. What happened there?
I hope any of this makes sense. Thanks
ofWES
My table is a record of scores from a game. There are 83 fields in it. Each segment of the game has 4 parts. There are 18 segments.
For each of the 18 segments there is an f, g, p and s, numbered for the segment. (f1, g1, p1,s1).
I want to cull a statistic from the table. In order for p to be valid, there must be a value to g. Essentially, g is a score but it is also true or false.
So;
SELECT avg(p1) FROM `rounds` WHERE `g1`= -1; gives me the average of the p for that segment when there is a g to validate it.
I have figured out that UNION will give me all the averages for the p's that are valid but I need to average those averages to make it the stat I want for that user ID.
I did run into a problem with the UNION command. When I put all 18 segments into the structure
(SELECT avg(p1) FROM `rounds` WHERE `g1`= -1)Union
(SELECT avg(p2) FROM `rounds` WHERE `g2`= -1)Union etc.....
I only got 16 rows returned. What happened there?
I hope any of this makes sense. Thanks
ofWES
So you want an average of all p1, p2, p3, etc based on all g1, g2, g3, etc colums?
How about something like this:
How about something like this:
Code: Select all
SELECT
AVG(p1+p2+p3+...)
FROM
`rounds`
WHERE
`g1` = -1 OR
`g2` = -1 OR
`g3` = -1 OR...Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Thank you for your reply.
You may have misunderstood or I did not say it clearly.
The 'g' is a stipulation for the 'p'. If there is no value for 'g', I cannot use the 'p'.
AVG(p1+p2+p3+...) adds the values of all 'p' together and averages them. When applying your suggestion, I get 5.6419 .
The values of p1, p2 and p3 are 1.9500, 2.0392, 1.9375 .....so the average of those is 5.9267 / 3 = 1.9755666
Hopefully, this clears up what I am looking to do.
You may have misunderstood or I did not say it clearly.
The 'g' is a stipulation for the 'p'. If there is no value for 'g', I cannot use the 'p'.
AVG(p1+p2+p3+...) adds the values of all 'p' together and averages them. When applying your suggestion, I get 5.6419 .
The values of p1, p2 and p3 are 1.9500, 2.0392, 1.9375 .....so the average of those is 5.9267 / 3 = 1.9755666
Hopefully, this clears up what I am looking to do.