BoostedMonkey BoostedMonkey - 3 days ago 5
MySQL Question

Counting Fields in Relation to their Column

I am trying to find the correct SQL to perform my task. I am trying to find the count of all actors that all act in the same movie. For all the movies. My SQL statement to fetch the film title and the first and last name of each actor is.

SELECT title, actor.first_name, actor.last_name,
FROM film
INNER JOIN film_actor ON film.film_id = film_actor.film_id
INNER JOIN actor ON film_actor.actor_id = actor.actor_id
ORDER BY film.title


My database returns this :

Return of SQL Query

As you can see there are 10 actors belonging to the movie title ACADEMY DINOSAUR. What is the SQL that would provide me with a number of actors for each film?

Answer

Your SQL can be like this

SELECT title, actor.first_name, actor.last_name, count(1) COUNT
FROM film
INNER JOIN film_actor ON film.film_id = film_actor.film_id
INNER JOIN actor ON film_actor.actor_id = actor.actor_id
GROUP BY title, actor.first_name, actor.last_name
Comments