Skip to content
Advertisement

counting individual elements that meet a certain criteria without duplicates in sqlite3

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
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement