mysql或平均或者我应该使用PHP？

Question

I am trying to find an average of an average with a mysql query.

I have 12 criteria and i can get an average for a column but i can not find the average of average,

Am trying to to work out a total rating of 12 criteria.

I think there are a few ways to go about this.

1. is to divide the total score by the number votes, problem is each row has a possibility of 12 votes.
2. get the avg totals, find our # of values set then divide the avg totals by # of values set

I need the number that i am dividing the total by to change depending on votes. 1 used can input 12 votes in 1 row 2 users could input a total of 18 votes in 2 rows, which means 1 user has not completed 6 votes The table has been shortened for testing purposes to columns that contain votes. I have posted this already, sorry for repost, but am starting to understand what help i roughly need

Answer 1

First, in order to decide whether a user has voted or not, you need to have something other than an integer stored in the field until a vote is added. I would suggest NULL.

Next, add another column to the table for the user's average rating. This would be calculated using the columns in the row that don't have a null value. Example semi-pseudo code:

// Get users row from database
// Use a query like: 
// SELECT comfort,service,friendliness,food,drinks,toilet,music FROM myTable WHERE user_id = 123;
$row = $db->fetchrow($userid);

// Loop through and find out how many are valid
// Also add values together
foreach($row as $col)
{
   if( !is_null($col) )
   {
      $numValidColumns++;
      $totalVote = $totalVote + $col; 
   }
}

// Calculate user's average
$userAvg = $totalVote/$numValidColumns;

// Add to db for this row in new field you just made
$db->insert(...);

Something like this would allow you to calculate the user's average save it. If they add another vote, you would recalculate this average.

The final step is to query all the rows and add the values of the user average column together. Divide it by the total number of rows to arrive at an overall average.

Hope that is on the right track for you.