Getting the average from multiple fields?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ofwes
Forum Newbie
Posts: 3
Joined: Mon May 28, 2007 6:19 pm

Getting the average from multiple fields?

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Please explain your table design and the purpose.
ofwes
Forum Newbie
Posts: 3
Joined: Mon May 28, 2007 6:19 pm

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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...
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
ofwes
Forum Newbie
Posts: 3
Joined: Mon May 28, 2007 6:19 pm

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Ah ya - missed that part.

Hmm, I don't know if that's possible. Sorry - I'm tapped out of ideas.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply