Jump to content

Archived

This topic is now archived and is closed to further replies.

jblaha

Trouble with SELECT

Recommended Posts

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,

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.