Search code examples
mysqlsqlrating

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 4 1

Solution

  • 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