SQL: is there a way to count the number of values from a table if there are none?-CodePudding

So i have 2 tables, one its "Movies"(movie,type,lenght) and one "Cast"(title_movie,id_actor) and i have to count in how many "SF" Movies every actor played.

I tried this:

SELECT id_actor, Count(type)
FROM Cast, Movies 
WHERE type='SF' and  title_movie=movie
GROUP BY id_actor;

and it only shows me the actors that has at least 1 SF movie, the ones with 0 movies are not displayed. Any ideas?

CodePudding user response：

LEFT JOIN to get all actors. To count SF movies only, use a case expression to do conditional aggregation.

SELECT id_actor, sum(case when type = 'SF' then 1 else 0 end)
FROM Cast
LEFT JOIN Movies ON title_movie = movie
GROUP BY id_actor;