I am having a table that stores ratings of a restaurant. As shown in the below image.
I am trying to get the average of all these columns in which I am being successful but I also want an average of all these averages as a main average.
I tried the below query but I am getting 3 as average rating which is not accurate. I think mysql is returning me a round value of the final result.
return $this->db->select('((ambience + music + service + value_for_money + cleanliness + sanitary + view)/7) as rating, AVG(ambience) as ambience, AVG(music) as music, AVG(service) as service,AVG(value_for_money) as value_for_money, AVG(cleanliness) as cleanliness, AVG(sanitary) as sanitary, AVG(view) as view' ) ->where('restaurant_id',$restaurantId) ->get('restaurant_ratings') ->row_array();
When I run the above query I get 3 as average for rating field.
The actual result would be 3.42.
Please help me understand what I am doing wrong and what can be done to achieve accurate result. Thanks
Advertisement
Answer
Just add AVG
to calculate rating:
$query = 'AVG(( ambience + music + service + value_for_money + cleanliness + sanitary + view )/7) as rating, AVG(ambience) as ambience, AVG(music) as music, AVG(service) as service, AVG(value_for_money) as value_for_money, AVG(cleanliness) as cleanliness, AVG(sanitary) as sanitary, AVG(view) as view'; return $this->db ->select($query) ->where('restaurant_id',$restaurantId) ->get('restaurant_ratings') ->row_array();