Jump to content
jarv

get avarage value of records in table - MySQL

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

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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>";
}

Share this post


Link to post
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";

Share this post


Link to post
Share on other sites

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.