Page 1 of 1

Getting the average from multiple fields?

Posted: Mon May 28, 2007 6:28 pm
by ofwes
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

Posted: Mon May 28, 2007 11:09 pm
by volka
Please explain your table design and the purpose.

Posted: Tue May 29, 2007 6:48 pm
by ofwes
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

Posted: Wed May 30, 2007 9:57 am
by pickle
So you want an average of all p1, p2, p3, etc based on all g1, g2, g3, etc colums?

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...

Posted: Wed May 30, 2007 5:06 pm
by ofwes
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.

Posted: Wed May 30, 2007 5:25 pm
by pickle
Ah ya - missed that part.

Hmm, I don't know if that's possible. Sorry - I'm tapped out of ideas.