MySQL view returns more than one row error when executed

Question

I have a view that keeps returning this error:

subquery returns more than one row

I am not good with sql and I am looking for a layman's explanation on why this happening and how I can solve it. My code is below:

Create View `RestaurantRatings` AS    
    SELECT
        p.restaurantName,
    (SELECT count(r.restaurantID) FROM RestaurantReviews) / (SELECT count(DISTINCT r.restaurantID) FROM RestaurantReviews) AS avg_num_votes,
    (SELECT avg(r.rating) FROM RestaurantReviews) AS avg_rating,
    count(r.restaurantID) as num_votes,
    avg(r.rating) as rating
FROM
    Restaurants p,
    RestaurantReviews r
where
     p.restaurantID = r.restaurantID
GROUP BY 
    restaurantName

All I want is to retrieve the ratings from the database using the Bayesian formula which I have been googling for the past couple of hours and I am not winning.

Thank you in advance

Answer 1

This is a tricky error, caused by a subtle problem in your query. Consider:

(SELECT count(r.restaurantID) FROM RestaurantReviews) 

You think this is doing an aggregation. However, it is not, because r.restaurantId is from the outer query, so even the count() is from the outer query. You could fix this by removing or adjusting the aliases in the subqueries.

Instead, just write a proper aggregation query with a proper join. The query would be something like this:

Create View `RestaurantRatings` AS    
    SELECT p.restaurantName,
           count(r.restaurantID) / count(DISTINCT r.restaurantID) AS avg_num_votes,
           avg(r.rating) AS avg_rating,
           count(r.restaurantID) as num_votes,
    FROM Restaurants p left join
         RestaurantReviews r
         on p.restaurantID = r.restaurantID
    GROUP BY  restaurantName;

I'm not exactly sure what you intend with the subqueries and the join. The above is a better place to start, though.