Re: MySQL: AVG in SELECT

first of all, when you do AVG(q18!='6') it will make the AVG of either TRUE=1, FALSE=0 maybe if you select q18, and AVG(q18) and when using the result query you decide from your PHP code whether to refer to the average or to its value... example: $result = mysql_query(" SELECT AVG(q1), AVG(q2), AVG(q3), AVG(q4), AVG(q5), AVG(q6), AVG(q7), AVG(q8), AVG(q11), AVG(q16), AVG(q18) as avg_q18,q18 FROM hr_form2 WHERE Answer != 0"); <? while ($row=mysql_fetch_Array($result)) { if ($row['q18']==6) $value = $row['avg_q18']; else $value = $row['q18']; } ?> hope it helps... "Tom Churm" <thomas.churm@pixelpark.com> wrote in message news:3BC5619E.8806F6F1@pixelpark.com... > hi, > > i have a select statement to give me the averages for various mysql > columns. for one of the columns, i need to take the average for only > those fields where the content !='6' (with 6 being a Flag telling me > to exclude it). so i came up with the following > select statement, which returns no mysql_error, but does not return the > correct figure for the AVG: > > SELECT AVG(q1), AVG(q2), AVG(q3), AVG(q4), AVG(q5), AVG(q6), AVG(q7), > AVG(q8), AVG(q11), AVG(q16), AVG(q18!='6') FROM hr_form2 WHERE Answer != > 0 > > from this i should get a return of something like this, since NONE of my > fields in column q18 contain a '6': > > AVG(q18) = 2.7143 > > instead, i get this: > > AVG(q18) = 1.0000 > > could someone please tell me--is there a logical error in my thinking > here? i want to select the AVG from q18 ONLY for those fields where > q18!=6 : for those fields where q18=6, I just want it left out of the > AVG. > > there's definitely a better way to formulate my sql, right? > > muchos gracias, > > tom