How can I retrive the top 3 rated events MySQL

Question

I have sporting events that users can rate. All data is stored in a Mysql database.

Ratings_table:

rating_id PK 
organization float //subrating
value_for_money float //subrating
fun_factor float //subrating
facilities float //subrating
event_id int
user_id int

Event_table:

event_id PK
event_name varchar

Is there a way I can use Mysql query to join the tables and order them so that I can retrieve the 3 events with the highest average rating of the sub ratings?

Do you want to get the 3 highest ratings, or do you want to get the 3 with the highest average ratings? I think you want the later, but your question asks for the former. — Styphon, Oct 03 '13 at 10:28
I want to get the top 3 events by the average of the 4 sub ratings. — crmepham, Oct 03 '13 at 10:29
What you want is probably easier to do in PHP than a single query. Whilst it is possible to do in one query, the query will be very complicated. — Styphon, Oct 03 '13 at 10:31

score 3 · Accepted Answer · answered Oct 03 '13 at 10:39

3

I think this may be it:

SELECT e.event_id, event_name, avg_rating
FROM Event_table e
JOIN (
    SELECT event_id, MAX((organization+value_for_money+fun_factor+facilities)/4) avg_rating
    FROM Ratings_table
    GROUP BY event_id
    ORDER by avg_rating DESC
    LIMIT 3) r
ON e.event_id = r.event_id

answered Oct 03 '13 at 10:39

Barmar

741,623
53
500
612

Huh, much simpler than I thought it would be. – Styphon Oct 03 '13 at 10:48

score 1 · Answer 2 · edited May 23 '17 at 10:25

This would be a very complex query to do it all in one single mysql query. Personally I would do this in PHP. This is an example of how you can do it in PHP and MySQLi

$ratings = array();
$query = "SELECT * FROM ratings";
$results = $dbConn->query($query);
//First we load all results into a big array.
while($row = $results->fetch_assoc($results) {
    if(!isset($ratings[$row['rating_id']]) {
        $ratings[$row['event_id']]['count'] = 0;
    }
    $ratings[$row['event_id']]['count']++;
    $ratings[$row['event_id']]['organisation'] += $row['organisation'];
    $ratings[$row['event_id']]['value_for_money'] += $row['value_for_money'];
    $ratings[$row['event_id']]['fun_factor'] += $row['fun_factor'];
    $ratings[$row['event_id']]['facilities'] += $row['facilities'];
}
//Now we go through each event and average out the results.
$averages = array();
foreach($ratings as $event_id => $data) {
    $averages[$event_id]['organisation'] = $data['organisation'] / $data['count'];
    $averages[$event_id]['value_for_money'] = $data['value_for_money'] / $data['count'];
    $averages[$event_id]['fun_factor'] = $data['fun_factor'] / $data['count'];
    $averages[$event_id]['facilities'] = $data['facilities'] / $data['count'];
}
//Now we can sort by whatever we want to:

Alternatively if you want the average across all 4 ratings you can use this bit on the end instead for the averages:

$averages = array();
foreach($ratings as $event_id => $data) {
    $averages[$event_id] = (($data['organisation'] + $data['value_for_money'] + $data['fun_factor'] + $data['facilities']) / 4) / $data['count'];
}

Please note: I have not tested this code... you should check it for bugs yourself.

For sorting, please check various stack overflow answers on sorting multidimensional arrays. Sort Multi-dimensional Array by Value or How do I Sort a Multidimensional Array in PHP

Thanks I appreciate the effort. – crmepham Oct 03 '13 at 10:52 — crmepham, Oct 03 '13 at 10:52

score 0 · Answer 3 · answered Oct 03 '13 at 10:26

0

Try this::

Select 
distinct r.eventId
from 
ratingTable r
inner join eventTable  e on (r.eventId=e.eventId)
order by rating_id limit 3

But it also can be achieved by

Select 
    distinct r.eventId
    from 
    ratingTable r
    inner join eventTable 
order by rating_id limit 3

answered Oct 03 '13 at 10:26

Sashi Kant

13,277
9
44
71

Why are you ordering by rating_id? He wants it ordered by the average rating. – Barmar Oct 03 '13 at 10:40

How can I retrive the top 3 rated events MySQL

3 Answers3