Select sdudios, actors and amount of films produced in a certain studio

Question

I continue experements with MySQL and have next. I need to select names of actors who starred in the films a certain studio. The selection includes the amount of films of the studio, which starred actor. The result is represented in the form of the table with the following columns: "Studio," "Actors", "Number of films". I have three table: actors, studios and films. actors and films have many-to-many, studios and film have many-to-many relationship. Actors and studios haven't relationship. So I can select studios, actors and films.

SELECT studios.title, CONCAT_WS(' ', actors.name, actors.surname), films.title 
FROM studios
JOIN studios_films ON studios.id = studios_films.studio_id
JOIN films ON films.id = studios_films.film_id
JOIN films_actors ON films.id = films_actors.film_id
JOIN actors ON actors.id = films_actors.actor_id

But how can I get not title of film but amount of films of the studio, which starred actor. I think I have to use aggregate (GROUP BY) Function COUNT. But when I use it in my query

SELECT studios.title, CONCAT_WS(' ', actors.name, actors.surname), COUNT(films.id) 
FROM studios
JOIN studios_films ON studios.id = studios_films.studio_id
JOIN films ON films.id = studios_films.film_id
JOIN films_actors ON films.id = films_actors.film_id
JOIN actors ON actors.id = films_actors.actor_id
GROUP BY filmstudios.id

I don't get, what I want. Can You help me to create query?Thanks!

You are almost there. You want to count the films for every studio / actor combination, right? That means, you have to add actors.id to your `GROUP BY` clause as well! — wolfgangwalther, Nov 02 '14 at 21:13
Its a pity but if use only `actors.is` you don't get wanted result((( — Antin, Nov 02 '14 at 21:35
**add** `actors.id` to it. `studios.id` should still remain in there. See my answer! — wolfgangwalther, Nov 02 '14 at 21:36

score 3 · Accepted Answer · edited May 23 '17 at 12:20

3

You can use the GROUP BY clause with multiple columns. This way you can count the number of films for every studio / actor combination.

Your complete query would look like this:

SELECT studios.title, CONCAT_WS(' ', actors.name, actors.surname), COUNT(films.id) 
FROM studios
JOIN studios_films ON studios.id = studios_films.studio_id
JOIN films ON films.id = studios_films.film_id
JOIN films_actors ON films.id = films_actors.film_id
JOIN actors ON actors.id = films_actors.actor_id
GROUP BY studios.id, actors.id

You can read more about multiple columns in GROUP BY clauses here:

Question on SO: Using group by on multiple columns

Tutorial: http://dba.fyicenter.com/faq/mysql/Use-Multiple-Columns-in-GROUP-BY.html

edited May 23 '17 at 12:20

Community

1
1

answered Nov 02 '14 at 21:18

wolfgangwalther

1,226
7
15

Thats great! Thanks! Please, tell me. Is it possible to create this selection without using JOIN? I mean realize this task using another features of MySQL? – Antin Nov 02 '14 at 21:33
No, I don't think so. JOIN is the way to go here, when dealing with multiple tables. – wolfgangwalther Nov 02 '14 at 21:37

Select sdudios, actors and amount of films produced in a certain studio

1 Answers1