I’m trying to count all the individual actors from movies in 2004 the problem is when I tried using the count() function it returned how much times every actor appeared in a movie that year. basically I cant get count() to play well with the GROUP BY function.
SELECT COUNT(name) FROM people INNER JOIN stars ON stars.person_id = people.id INNER JOIN movies ON stars.movie_id = movies.id WHERE movies.year = 2004 GROUP BY name;
relevant tables: movies (id, title, year), stars (movie_id, person_id), people (id, name)
Advertisement
Answer
You just need to count the number of DISTINCT
person_id
in stars
(no need to use the people
table at all) that have been in movies made in 2004:
SELECT COUNT(DISTINCT person_id) AS num_actors FROM stars JOIN movies ON movies.id = stars.movie_id WHERE movies.year = 2004