123 123 - 28 days ago 5
MySQL Question

SQL - Find the first name, last name and total combined film length of Sci-Fi films for every actor

I have a query that accomplishes this, but it excludes actors who have never been in a Sci-Fi film. I want to include those actors even though their total length of Sci-Fi films is zero.

The result should list the names of all of the actors(even if an actor has not been in any Sci-Fi films) and the total length of Sci-Fi films they have been in.

I have the following query, I just don't know how to modify it to include all actors.

SELECT actor.first_name, actor.last_name, SUM(film.length)
FROM actor
INNER JOIN film_actor ON actor.actor_id = film_actor.actor_id
INNER JOIN film on film_actor.film_id = film.film_id
INNER JOIN film_category ON film_actor.film_id = film_category.film_id
INNER JOIN category ON film_category.category_id = category.category_id
WHERE category.name = 'Sci-Fi'
GROUP BY category.name, actor.actor_id
ORDER BY actor.first_name


I'm using the following schema:


Answer

Besides switching to Outer Joins you might also use conditional aggregation:

SELECT actor.first_name, actor.last_name,
   SUM(case when category.name = 'Sci-Fi' then film.length else 0 end)
FROM actor
INNER JOIN film_actor ON actor.actor_id = film_actor.actor_id
INNER JOIN film on film_actor.film_id = film.film_id
INNER JOIN film_category ON film_actor.film_id = film_category.film_id
INNER JOIN category ON film_category.category_id = category.category_id
GROUP BY category.name, actor.actor_id
ORDER BY actor.first_name

This will exclude actors who never made a film.