SQL query using sum()

Question

Hello I'm currently working on SQL problem that I can't quite figure out. Here is the Schema I'm working with:

enter image description here

Here is the question I am stuck on:

-- 3 Find the first name, last name and total combined film length of Sci-Fi films for every actor. That is 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.

So far I 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 I need to group it by actor_id but i'm unable to do this in a select subquery. Anyone have some tips?

Do you have the option to export the graphical representation of the schema into a programmatic text representation? I.e. CREATE TABLE IF NOT EXISTS film /* ... */ — George Bahij
– George Bahij, Commented Jul 17, 2015 at 23:19

Bill · Accepted Answer · 2015-07-17 23:30:12Z

4

There is no need to use a subquery. Aggregate functions work on the entire data set. The 'group by' specifies how to group the data you're aggregating.

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
;

The outer joins ensure actors with no sci-fi film experience are included in the results by

edited Jul 17, 2015 at 23:30

answered Jul 17, 2015 at 23:24

Bill

3141 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Funk Over a year ago

I don't think you can have first_name and last_name in the SELECT list because they are not in the GROUP BY clause

Gordon Linoff Over a year ago

@apfunk . . . MySQL allows that syntax. In fact, assuming that a.actor_id is unique, this usage even conforms with the ANSI standard.

Dienes László · Accepted Answer · 2015-07-17 23:23:48Z

1

I don't understand what are you need subquery, try this:

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;

answered Jul 17, 2015 at 23:23

Dienes László

844 bronze badges

3 Comments

l0aded1 Over a year ago

I had that before but it is getting the film.length for every category of movie where I just need the film.length for the Sci-Fi movies. The actors that are not in Sci-Fi should have a sum of 0. If that makes sense. Thank you though!

David Soussan Over a year ago

@Dienes: "even if an actor has not been in any Sci-Fi films" so INNER JOINS are not what is needed here.

l0aded1 Over a year ago

Ah... It's my joins. Maybe I need to go over the Joins in SQL.

Jman · Accepted Answer · 2016-04-27 18:14:55Z

1

This should get you exactly what you want, including the part about having actors that aren't in Sci-Fi movies. You can LEFT JOIN on film to include all films the film_actor is in. The additional AND statement works with the LEFT JOIN to include actors not in Sci-Fi movies for your aggregate sum function.

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;

edited Apr 27, 2016 at 18:14

answered Jul 20, 2015 at 4:20

Jman

112 bronze badges

Comments

Andrew Brown · Accepted Answer · 2015-11-09 01:48:09Z

The top answer here actually is not correct. This will work:

   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 actors, and combines them with the sci-fi actors and gives the combined screen time for sci-fi movies for all. Test it on your data set, worked for me.

Collectives™ on Stack Overflow

SQL query using sum()

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related