How to calculate the average number of rating star-CodePudding

i has a query a table in db

SELECT book_id,rating_date
from review r
ORDER BY book_id, rating_start

and result like

id | book_id | rating_date
1  |    3    |     1
5  |    3    |     2
6  |    3    |     3
2  |    5    |     3
7  |    5    |     4
9  |    5    |     5

how i display average number of rating star of each book with formula like

AR = (1*a 2*b 3*c 4*d 5*e) / (a b c d e)
o Where AR is the average rating
o a is the number of 1 star ratings
o b is the number of 2 star ratings
o c is the number of 3 star ratings
o d is the number of 4 star ratings
o e is the number of 5 star ratings

CodePudding user response：

Just normal AVG values based on book_id.

   SELECT AVG(rating_star) FROM review GROUP BY book_id;

CodePudding user response：

This can be done by simply using the AVG() function as follows:

SELECT AVG(rating_star)
FROM review

Edit: I assumed rating_start was a typo