Sql query to find rating?

In my application I'm storing reviews given by users into mysql.

Now I have to find rating based on reviews.I am storing review like "food quality,Service,Atmosphere,Value", all have values between 1 to 5.

How can I find rating based on these values.

My table structure is

FoodQuality Service Atmosphere Value RestaurantId 1 3 2 开发者_C百科 4 1

Assuming that you want rating per restaurant id, and because you didn't specify what do you mean by rating

SELECT RestaurantId
, SUM(FoodQuality+Service+Atmosphere+Value) rating1 -- the sum of total rating
, AVG(FoodQuality+Service+Atmosphere+Value) rating2 -- the average of total rating, max 20
, AVG((FoodQuality+Service+Atmosphere+Value)/4) rating3 -- the average of average rating, max 5
FROM table
GROUP BY RestaurantId

There are some naive solutions in SQL, but I would recommend that you read this article: http://www.evanmiller.org/how-not-to-sort-by-average-rating.html and try to implement such a rating somewhere directly in your code.

That said a naive solution could be to say 4 and 5 are positive (for example):

Pseudocode:

myrating = (SELECT rating FROM reviews WHERE rating == 4 OR rating == 5) AS positivratings / (SELECT * FROM reviews) AS totalrating

Exactly you have to provide us the table details like there may be ratings per user then you have to use group by clause and AVG() mysql function to find rating per user.

Give us some tables structure. You can use the [AVG()][1] function. For further details give the tables structure please.

SELECT products.*, AVG(r.value) AS product_rating FROM products p LEFT JOIN ratings r ON p.id = r.product_id GROUP BY p.id

Considering that products is the table that you have ratings for and ratings is the table you hold the ratings in, ratings.value being the field that contains the value of the rating gaved.