get avarage value of records in table - MySQL

[jarv]

Hi,

 

I have a table RATINGS

 

here is an example:

 
<table border="1" cellpadding="2" cellspacing="2">
<tr>
<td>rating_id</td>
<td>review_id</td>
<td>rating_name</td>
<td>rating</td>
</tr>
 
<tr>
<td>1</td>
<td>46</td>
<td>location</td>
<td>4</td>
</tr>
 
<tr>
<td>2</td>
<td>46</td>
<td>accommodation</td>
<td>3</td>
</tr>
<tr>
<td>3</td>
<td>46</td>
<td>travel time</td>
<td>5</td>
</tr>
</table>

 

I would like to get the average from the RATING datafield for each of the rows in my database

Could anyone please help point me to a tutorial or be so kind to post some example code?

 

thanks

Edited August 26, 2015 by jarv

[Barand]

You have to define for what you want the average

 

eg

 

average rating for each review

SELECT review_id, AVG(rating)
from tablename
GROUP BY review_id

average rating for each rating_name

SELECT rating_name, AVG(rating)
from tablename
GROUP BY rating_name

average for the whole table

SELECT AVG(rating) from tablename

[jarv]

Thanks but no, I need to get the average by adding each rating for each review

 

Review1 = Rating: Location = 4

Review2 = Rating: Location = 4

Review3 = Rating: Location = 5

 

4+4+5 / 3reviews = Overall Location = 4.3

 

Review1 = Rating: Accommodation = 2

Review2 = Rating: Accommodation = 5

Review3 = Rating: Accommodation = 1

 

2+5+1 / 3reviews = Overall Accommodation = 2.6

[jarv]

here is the code in my page:

 

$SQL = "
SELECT sr.review_id,
sr.review_date,
sr.review,
sr.reviewer_name,
sr.reviewer_surname,

r.rating_name,
r.rating
FROM
school_reviews sr
LEFT JOIN
ratings r USING(review_id)
WHERE
sr.active = 1
AND sr.is_deleted = 0
AND sr.school_id = '" . $school_id . "'
ORDER BY
sr.review_date DESC";
$q->query($DB,$SQL);

// store results of query in array
$reviews = array();

while($row = $q->getrow())
{
// get the review id
$id = $row['review_id'];
// group reviews by review id
if(!isset($reviews[$id]))
{
$reviews[$id] = array(
'message' => $row['review'],
'date' => $row['review_date'],
'name' => $row['reviewer_name'],
'surname' => $row['reviewer_surname'],
'ratings' => array()
);
}

// group ratings by review id
$reviews[$id]['ratings'][] = array(
'name' => $row['rating_name'],
'value' => '<img src="/en/images_v2/ratings/star'.$row['rating'].'.png" />'
);
}

// loop over the reviews
foreach($reviews as $review_id => $review)
{
// output review
echo "<div class=\"schoolreview\">";
echo "<p><span class=\"right\">Posted On: " . $review['date'] . "</span>";
echo "Review by: ".$review['name']."<br />\n"
. "</p>\n";

// output ratings list for each review
echo "<ul class=\"ratings\">\n";
foreach($review['ratings'] as $rating)
{
echo "\t<li>" . $rating['name'] . ': ' . $rating['value'] . "</li>\n";
}
echo "</ul>\n";
echo "<p>Review: " . nl2br($review['message']) . "</p>";
echo "</div>";
}

[Barand]

try

$SQL = "
    SELECT sr.review_id,
        sr.review_date,
        sr.review,
        sr.reviewer_name,
        sr.reviewer_surname,

        r.rating_name,
        r.rating
    FROM
        school_reviews sr
    LEFT JOIN
        ratings r USING(review_id)
    WHERE
        sr.active = 1
        AND sr.is_deleted = 0
        AND sr.school_id = $school_id
    ORDER BY
        sr.review_date DESC";
$q->query($DB,$SQL);

// store results of query in array
$reviews = array();
$totals = array();                                                     // ADDED
$ratingsCount = 0;                                                     // ADDED

while($row = $q->fetch_assoc())
{
    // get the review id
    $id = $row['review_id'];
    // group reviews by review id
    if(!isset($reviews[$id]))
    {
        $reviews[$id] = array(
        'message' => $row['review'],
        'date' => $row['review_date'],
        'name' => $row['reviewer_name'],
        'surname' => $row['reviewer_surname'],
        'ratings' => array()
    );
    }

    // group ratings by review id
    $reviews[$id]['ratings'][] = array(
    'name' => $row['rating_name'],
    'value' => str_repeat('* ', $row['rating'])
    #'value' => '<img src="/en/images_v2/ratings/star'.$row['rating'].'.png" />'
    );
    
    // rating totals                                                                // ADDED
    $ratingsCount++;
    if (isset($totals[$row['rating_name']])) {
        $totals[$row['rating_name']] += $row['rating'];
    }
    else {
        $totals[$row['rating_name']] = $row['rating'];
    }
}

// loop over the reviews
foreach($reviews as $review_id => $review)
{
    // output review
    echo "<div class=\"schoolreview\">";
    echo "<p><span class=\"right\">Posted On: " . $review['date'] . "</span>";
    echo "Review by: ".$review['name']."<br />\n"
    . "</p>\n";

    // output ratings list for each review
    echo "<ul class=\"ratings\">\n";
    foreach($review['ratings'] as $rating)
    {
        echo "\t<li>" . $rating['name'] . ': ' . $rating['value'] . "</li>\n";
    }
    echo "</ul>\n";
    echo "<p>Review: " . nl2br($review['message']) . "</p>";
    echo "</div>";
}

// loop over the totals                                                           // ADDED
$reviewCount = count($reviews);
echo "Average ratings\n<ul>\n";

foreach ($totals as $name => $tot) {
    $avg = number_format($tot/$reviewCount, 2);
    echo "<li>$name : $avg</li>\n";
}
echo "</ul>\n";

$overallAverage = number_format(array_sum($totals)/$ratingsCount, 2);
echo "Overall average rating : $overallAverage";