SQL query using sum ()

Question

Hello, I am currently working on an SQL problem that I cannot understand. Here is the diagram I am working with:

Here is the question I'm stuck on:

- 3 Find the first name, last name and total length of the film for films of any level. As a result, the names of all participants should be indicated (even if the actor was not in any Sci-Fi films) and the total length of the Sci-Fi films in which they were.

I still have

SELECT actor.first_name, actor.last_name, (SELECT SUM(film.length)
from film 
INNER JOIN film_category
ON film.film_id = film_category.film_id
INNER JOIN category
ON film_category.category_id = category.category_id
INNER JOIN film_actor
ON film_actor.film_id = film.film_id
INNER JOIN actor
ON film_actor.actor_id = actor.actor_id
WHERE category.name = 'Sci-fi'
)
from actor

I know that I need to group it using actor_id, but I cannot do this in the select subquery. Does anyone have any clues?

Answer 1

. . "Group by" , , .

select a.actor_id, a.first_name, a.last_name, sum(f.length)
  from actor a
  left outer join film_actor fa on fa.actor_id   = a.actor_id
  left outer join film       f  on f.film_id     = fa.film_id
  left outer join film_categories       fc on fc.film_id    = f.film_id
  left outer join categories            c  on c.category_id = fc.category_id
 where c.name = 'sci-fi'
  group by a.actor_id
;

, , ,

Answer 2

, , :

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

Answer 3

, , , Sci-Fi. , , film_actor. AND LEFT JOIN, Sci-Fi .

SELECT a.actor_id, a.first_name, a.last_name, sum(f.length) AS length
FROM actor a
INNER JOIN film_actor fa ON fa.actor_id = a.actor_id
INNER JOIN film_category fc ON fc.film_id = fa.film_id
INNER JOIN category c ON c.category_id = fc.category_id
LEFT JOIN film f ON f.film_id = fa.film_id 
AND c.name = 'Sci-Fi'
GROUP BY a.actor_id; 

Answer 4

. :

   SELECT T1.first_name, T1.last_name, T2.total
    FROM 
        (SELECT a.first_name, a.last_name, a.actor_id
         FROM actor a
         ) 
        AS T1
    LEFT JOIN 
        (SELECT a.first_name, a.last_name, a.actor_id, SUM( f.length ) AS total, c.name
        FROM actor a, film_actor fa, film f, film_category fc, category c
        WHERE c.category_id = fc.category_id
        AND fc.film_id = f.film_id
        AND a.actor_id = fa.actor_id
        AND fa.film_id = f.film_id
        AND c.name =  'sci-fi'
        GROUP BY a.actor_id) 
        AS T2 
    ON T1.actor_id = T2.actor_id;

It takes all the actors and combines them with science fiction actors and gives combined time to watch movies for all films. Check it out on your data set, worked for me.