'Which production house has produced the most number of hit movies (average rating > 8)?

[![ERD][1]][1]

SELECT m.production_company, COUNT(r.movie_id) as movie_count,
RANK() OVER(
    ORDER BY 
    COUNT(r.movie_id) DESC
    ) prod_company_rank
FROM movie as m
INNER JOIN ratings as r
ON m.id = r.movie_id
WHERE r.avg_rating > 8
GROUP BY production_company;

[![Output][2]][2]

The result has correct production company but I don't understand why is NULL there, it had to be Dream Warrior and National Theatre Live Please help me where am doing wrong Thanks in advance! [1]: https://i.stack.imgur.com/gtnuN.png [2]: https://i.stack.imgur.com/6rs8T.png

mysqlsql


Solution 1:[1]

NULL is there because it is in your data. You can see these rows using:

select m.*
from movies m
where m.production_company is null;

If you don't want them, then filter them out using:

where m.production_company is not null

in your query.

Solution 2:[2]

use this in where clause

WHERE avg_rating >8 AND production_company IS NOT NULL

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gordon Linoff
Solution 2 divya Goel

Related Questions