SQL Query giving wrong output

I have written this code to find “For each year, count the number of movies in that year that had only female actors”.

                                WITH

                                k AS

                                (SELECT MC.MID a
                                FROM M_CAST MC
                                JOIN PERSON P ON TRIM(P.PID) = TRIM(MC.PID)
                                WHERE TRIM(P.GENDER) IN ('Male', 'None'))

                                SELECT CAST(SUBSTR(M.year,-4) AS UNASSIGNED) Year, COUNT(DISTINCT TRIM(MID)) number_of_movies
                                FROM MOVIE M
                                WHERE TRIM(MID) NOT IN (SELECT a FROM k) 
                                GROUP BY CAST(SUBSTR(M.year,-4) AS UNASSIGNED)
                                ORDER BY Year 

I am getting this output –

    Year    Female_Movie_Count
    1939    1
    1999    1
    2000    1
    2009    1
    2012    1
    2018    2

When i had submitted this code, then i get response that “your output is wrong. when selecting non-female movies you should also include MID’s which have null PID’s in M_cast table as non-female movies too”. Hence i am a beginer in sql, i am not getting that where i am wrong. Please suggest me that where i need to modify this code.

Here is the schema – https://i.stack.imgur.com/sWRSN.png

Answer

You could use a UNION ALL with M_CAST where PID is NULL as per your requirement

                         WITH
                            k AS
                            (SELECT MC.MID a
                            FROM M_CAST MC
                            JOIN PERSON P ON TRIM(P.PID) = TRIM(MC.PID)
                            WHERE TRIM(P.GENDER) IN ('Male', 'None')
                            UNION ALL
                            SELECT MC.MID a
                            FROM M_CAST MC
                            WHERE MC.PID IS NULL 
                              )

                            SELECT CAST(SUBSTR(M.year,-4) AS UNASSIGNED) Year, 
                            COUNT(DISTINCT TRIM(MID)) number_of_movies
                            FROM MOVIE M
                            WHERE TRIM(MID) NOT IN (SELECT a FROM k) 
                            GROUP BY CAST(SUBSTR(M.year,-4) AS UNASSIGNED)
                            ORDER BY Year