Averages and Sums and Such....

[james0816]

I'm not sure about the best way to do this so here goes. First, I have retrieved from my database only certain records:

$result2 = mysql_query("SELECT * FROM results where dcode=$dname and yr=$lyr", $connection);

we'll keep it short by only listing a few records:

a b c
20 12 257
16 8 312
32 40 118


now...here's where it is getting tricky for me.

I want to display averages for a and b and a total for c. to complicate it, i also want to count the number for b that is under 10.

so my display should be something to the effect of:

a b c d
22.7 20 687 1 <- D represents one row under 10

is there an easy of doing this or am i going to have to write individual queries for each item i am trying to display?

thx

 

[aardvark92]

Try something like this:

SELECT AVG(a), AVG(b), SUM(c), COUNT(IF(b < 10, 1, 0)) FROM results where dcode=$dname and yr=$lyr

I'm not where I can test it so I can't guarantee the syntax is correct, but this should at least get you started.

 

[james0816]

tried something similar but it doesn't work:

$result3 = mysql_query("SELECT AVG(a) as ava, AVG(b) as avb FROM results where dcode=$dname and yr=$lyr", $connection);

echo mysql_result($result3,"ava");

I will display only one average (ava).

If I change the echo statement for avb, it still displays the data from ava.

don't understand that one.

 

[james0816]

i have it working now but even more confused.

i wound up coding it like this:

echo mysql_result($result3,0,0) for avs
echo mysql_result($result3,0,1) for avf

it displayed fine that way....but why not by name??

 

[james0816]

ok...i got the averages and sums to come out right...now for the counting part...

SELECT AVG(a), AVG(b), SUM(c), COUNT(IF(b < 10, 1, 0)) FROM results where dcode=$dname and yr=$lyr

COUNT(IF(b < 10, 1, 0)) - This returns 3 when it should only return 1.

 

[aardvark92]

Oops! It should be:

SUM(IF(b < 10, 1, 0))

Sorry about that!

 

[james0816]

np...that got er done. comin together now. thx