user2927848 user2927848 - 7 months ago 19
SQL Question

MySQL Sum only specific category 0 otherwise

There are some questions already like this, but I haven't found one exactly the same.

SELECT A.first_name, A.last_name, SUM(F.length)
FROM actors A
LEFT JOIN film_actors FA ON A.actor_id=FA.actor_id
LEFT JOIN film F ON FA.film_id=F.film_id
LEFT JOIN film_category FC ON FC.film_id=F.film_id
LEFT JOIN category C ON FC.category_id=C.category_id
WHERE C.name="Sci-Fi"
GROUP BY A.last_name, A.first_name;


My issue is that I need to ONLY sum sci-fi for the sum, but I need report back all actors. If an actor has never been in a sci-fi movie they should be in the returned list but have 0 for their length. The
WHERE
is cutting out the ones with that don't have Sci-Fi movies which is the problem I'm just not sure how else to do it.

Answer

try with a CASE .... WHEN clause:

SELECT A.first_name, A.last_name,
  SUM(CASE WHEN C.name="Sci-Fi" THEN F.length ELSE 0 END) 
FROM actors A 
LEFT JOIN film_actors FA ON A.actor_id=FA.actor_id 
LEFT JOIN film F ON FA.film_id=F.film_id 
LEFT JOIN film_category FC ON FC.film_id=F.film_id 
LEFT JOIN category C ON FC.category_id=C.category_id
GROUP BY A.last_name, A.first_name;