Jump to content

get avarage value of records in table - MySQL


jarv

Recommended Posts

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 by jarv
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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>";
}

Link to comment
Share on other sites

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";
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.