Jump to content


Photo

Trouble with SELECT


  • Please log in to reply
No replies to this topic

#1 jblaha

jblaha
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 10 July 2003 - 10:17 PM

I have data that looks like this:

userID Column1 Column2 Column3 Column4
1...........0..............7..............6..............5
2...........7..............4..............7..............6
1...........8..............0..............7..............7
1...........9..............9..............9..............0
2...........5..............5..............5..............5

I want one query that finds the average of each column where the userID =1, and only includes the values in the average of each column if they are >0. That means that if the userID is 1 that the average of Column1 should be 8.5 (excluded the zero in row 1) but the average of Column 3 should be 7.3333 (row 1 is included).

Right now I can get the average for each column including the zero rows, but when I start to add WHERE Column1 !=0 it excludes the entire row for the averages of other columns as well. The average for Column1 becomes 8.5 (excludes row1 properly) but the average for Column3 is 8.0 (excludes row1 also)

Here is the code I am using:

    $query = "SELECT avg(Column1),                       avg(Column2),                       avg(Column3),                       avg(Column4),                       avg(Column5),                       avg(Column6)               FROM table t               WHERE t.userID = ".$clientrow["userid"]."               AND t.Column1 !=0               ORDER BY t.datemodified";     if (!($result = @ mysql_query($query, $connection)))        showerror();       $ratingrow = mysql_fetch_array($result);

Is there a way to exclude rows selectively for each column inside one table to do what I want???

Thanks,




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users