Get average of average columns in mysql

Question

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

Answer 1

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();