Wayne Allen Wayne Allen - 1 year ago 52
MySQL Question

pdo list top 5 using avg?

Ok once again I am struggling making my movie rating system :(

My table structor is

ID | UserID | Rating | TMDB | TYPE
-----------------------------------
1 34 6 432 2
-----------------------------------
2 34 9 432 3
-----------------------------------
3 44 9 468 2


Thanks to user Barmar I was able to work out the average using

$sql = "SELECT AVG(rating) AS avg_rating FROM `tbl_rating` WHERE `tmdb`= :tmdb AND `type`= :type ";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':tmdb', $tmdb);
$stmt->bindParam(':type', $type);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$avg_rating = $row['avg_rating'];


Now I want to make a top 5 list of gorey horrors (type = 2). I have absolutely no clue how to work this out :( I know i can limit the results by a simple LIMIT command at the end and I think ORDER by AVG(rating) is need? and I need to drop the WHERE tmdb. I have no idea how to achive this could anyone help please!

Answer Source

You can modify your query to group by tmdb (instead of filter by), order the results by avg_rating (descending) and limit the rows returned to 5. Something like this:

SELECT `tmdb`, AVG(rating) AS avg_rating
FROM `tbl_rating`
WHERE `type`= :type
GROUP BY `tmdb`
ORDER BY avg_rating DESC
LIMIT 5

Proof of concept:

You can run the following SQL commands in the MySQL console to validate the query returns sensible results on your sample data.

create table tbl_rating (
ID int unsigned primary key auto_increment,
UserID int unsigned,
Rating smallint unsigned,
tmdb int unsigned,
type int unsigned) engine=innodb;

insert into tbl_rating
(UserId, Rating, tmdb, `type`) values
(34, 6, 432, 2),
(34, 9, 432, 3),
(44, 9, 468, 2);

SELECT `tmdb`, AVG(rating) AS avg_rating
FROM `tbl_rating`
WHERE `type`= 2
GROUP BY `tmdb`
ORDER BY avg_rating DESC
LIMIT 5

+------+------------+
| tmdb | avg_rating |
+------+------------+
|  468 |     9.0000 |
|  432 |     6.0000 |
+------+------------+
2 rows in set (0.01 sec)