用PHP计算总平均评分

How would I go about running a PDO query and then processing the results with PHP to output an Average rating for a specific blank based on user inputted reviews?

Each review is weighted on 2 criterias, Service and Price. Both of this are 0-5 therefore the end average is weighted out of 10. Per review bases I currently use the following code to calculate the Average Rating for the specific review.

$rating_total = $row['review_service_rating'] + $row['review_price_rating'];

$average_rating = ($rating_total / 100) * 100;

So for each review there are 2 sql rows for review_service_rating and review_price_rating both of this are out of 5.

However I'm not exactly sure how to Count all of the ratings and then determine base grade for the specific page out of 10.

So let's say we have 3 reviews. This reviews are 7/10, 8/10 and 10/10. Logically we would add up all of the numbers and then add up all of /10 giving us. 25/30 which translates to 83.33 or in my case 8/10. This are the 2 steps I'm uncertain how to do.

1 - Get all ratings from SQL and count them

2 - Determine Average /10 based on all ratings

Current PDO code to pull data from DB as well as Count of how many Reviews each particular user has.

$q = $db->prepare("SELECT * FROM reviews WHERE assigned_id = :review_id ORDER BY review_posted DESC");
         $q->execute(array(':review_id' => $_GET['page_id']));

         $r = $q->fetchAll(PDO::FETCH_ASSOC);

         echo '<br><div class="well">Average Rating</div><br>';

         foreach($r as $row) {

         $q = $db->prepare("SELECT * FROM users WHERE user_id = :review_user_id");
         $q->execute(array(':review_user_id' => $row['user_id']));
         $r = $q->fetch(PDO::FETCH_ASSOC);

         $q = $db->prepare("SELECT COUNT(*) FROM reviews WHERE user_id = :review_user_id");
         $q->execute(array(':review_user_id' => $row['user_id']));
         $user_reviews = $q->fetchColumn();

enter image description here

enter image description here

enter image description here

Assume each user give x point (limit by 10), you must sum them up (x) then divide it by rating_times, you get average rating.

For example

 User 1 rate 7
 User 2 rate 6 
 User 1 rate 5

 => (7+6+5)/3 = 6

So you just add one more field when user rating called point. Then use query SUM to SUM point up, divided with query COUNT then done. forget the (x**/10**).

Finally, use 2 average rating (Price and service), sum of them, divided by 2 and you got overall rating.

Here's what you probably meant to do:

$q = $db->prepare("
      SELECT * 
      FROM review r 
          JOIN users u ON r.user_id=u.id  
          JOIN (SELECT user_id, COUNT(1) as count FROM review GROUP BY user_id) uc ON uc.user_id = u.id 
      WHERE r.assigned_id=:review_id 
      ORDER BY r.review_posted DESC");
$q->execute(array(':review_id' => $_GET['page_id']));
$r = $q->fetchAll(PDO::FETCH_ASSOC);

foreach ($r as $row) {
     // $row should have all fields included in $tables review and users as well as $row[count] would contain the total number of reviews a user made
}

This will include the user's details alongside the rating details for each review with that assigned_id

Edit: Added sum of user reviews as well.