I have a table 'movies' with fields movie_id,movie_name,language,cast and another table 'ratings' with fields id,movie_id,rating and comment.
I need to fetch all movies details with average and total count of ratings.
If there is no rating for any movie, average rating and total ratings count must be zero.
for this i tried this MySQL query:
SELECT m.movie_id,movie_name,cast,language,AVG(rating) as average_rating,COUNT(rating) as total_rating FROM movies m LEFT JOIN ratings r ON m.movie_id=r.movie_id;
group by with
movie_id so it will differentiate your result with unique movie_id.
SELECT m.movie_id,m.movie_name,m.cast,m.language,AVG(r.rating) as average_rating,COUNT(r.rating) as total_rating FROM movies m LEFT JOIN ratings r ON m.movie_id=r.movie_id GROUP BY m.movie_id;