CODEWITHSUNDEEP
sqlsqlite
willay
willay

Reputation: 1

SELECT interfering with COUNT in SQLite

I'm working with 3 tables: actors, films, and actor_film. Actors and films only have 2 fields: id (primary key) and name. Actor_film also has 2 fields, actor and film, which are both foreign keys representing actor and film ids, respectively. So if a film had 4 actors in it, there'd be 4 actor_film entries with the same film and 4 different actors.

My problem is that I'd like to return the film id, film name, and a count of the number of actors in a certain movie. However, instead of being over the whole table, I'd like it to be only for films with a certain actor in them.

Currently, when I query something like:

SELECT af.film, f.name, COUNT(af.film) FROM film AS f, actor_film AS af WHERE af.actor = "/m/0f502" AND af.film = f.id GROUP BY af.film;

I'm getting

/m/0f4_l|Pulp Fiction|1 /m/0zz10|Primary Colors|1

which is 75% correct, but not the full story. The first 2 columns are right, but I'd like the count to not just be the number of times the actor showed up in the movie (obviously capped at 1), but the total number of actors in those movies as counted in the actor_film table.

I think this requires a nested SELECT or INNER JOIN, but I'm new to SQLite and don't really understand how that works. Any help and explanations would be thoroughly appreciated.

Upvotes: 0

Views: 136

Answers (3)

CL.
CL.

Reputation: 180162

This is indeed possible with nested SELECTs, i.e., (correlated) subqueries:

SELECT id,
       name,
       (SELECT count(*)
        FROM actor_film
        WHERE film = films.id
       ) AS actors
FROM films
WHERE id IN (SELECT film
             FROM actor_film
             WHERE actor = '/m/0f502');

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521987

The simplest option which came to mind was to aggregate over the actor_film table and count two things. First, count the number of actors in each movie. At the same time, count the number of times the given actor of interest appears in that movie. Films are retained in this subquery if the actor we want appears at least once. Then, join this subquery to the film table to bring in the film's ID and name.

SELECT f1.id, f1.name, f2.num_actors
FROM film f1
INNER JOIN
(
    SELECT film, COUNT(*) AS num_actors
    FROM actor_film
    GROUP BY film
    HAVING SUM(CASE WHEN actor = "/m/0f502" THEN 1 ELSE 0 END) > 0
) f2
    ON f1.id = f2.film

Upvotes: 2

jfriesenhahn
jfriesenhahn

Reputation: 305

You're using the actor column in actor_film basically to hone in on which movies you want to get a count of. Your next step should be to JOIN that result back to actor_film so you can grab all actor_film records related to that film.

I believe the following should do what your looking for:

SELECT f.id, f.name, COUNT(af_2.actor) AS actor_count
FROM actor_film af
JOIN actor_film af_2 ON
    af_2.film = af.film
JOIN film f ON
    f.id = af.film
WHERE af.actor = "/m/0f502"
GROUP BY af.film;

Upvotes: 0

Related Questions