在单个计数列中对SQL中的空值和非空值进行计数

Question

我的代码片段

movie_female_cast = pd.read_sql_query(""" SELECT TRIM(MC.MID), COUNT(P.Gender),P.Gender FROM M_Cast MC
INNER JOIN Person P
ON P.PID = TRIM(MC.PID)
WHERE P.Gender IN ('Male',NULL, 'Female')
GROUP BY MC.MID 
""",conn)
\

——输出"

TRIM(MC.MID)  COUNT(P.Gender)  Gender
0       tt0021594                8    Male
1       tt0026274               20    Male
2       tt0027256               13    Male
3       tt0028217               10    Male
4       tt0031580               44  Female
...           ...              ...     ...
3468    tt8581230                8  Female
3469    tt8698956               10    Male
3470    tt8852558               17    Male
3471    tt8932884                3    Male
3472    tt9007142                9    Male
'''

在这里的p .性别栏，有3个类别-女性，男性，Null。现在我想找到电影id，上面提到的所有3个类别的计数，现在上面的代码计数实际上并没有考虑NULL值，只返回男性和女性类别。有谁能帮帮我吗

Answer 1

对于count，不指定p. gender:

movie_female_cast = pd.read_sql_query(""" SELECT TRIM(MC.MID), COUNT(*),P.Gender FROM M_Cast MC
INNER JOIN Person P
ON P.PID = TRIM(MC.PID)
WHERE P.Gender is NULL or p.Gender IN ('Male','Female')

GROUP BY MC.MID, p.gender 
""",conn)