CODEWITHSUNDEEP
sqlsql-server
SummerDays
SummerDays

Reputation: 57

SQL Subquery to get actors in more than 30 films

I need to find all the names of actors that have participated in more than 30 films. This below isn't quite doing it. What am I doing wrong here?

SELECT first_name, last_name
FROM actor A
WHERE 30 > (SELECT COUNT(actor_id) FROM film_actor);

Tables involved:

Upvotes: 0

Views: 607

Answers (4)

Shushil Bohara
Shushil Bohara

Reputation: 5656

This is also good alternative, if we have common columns then it would better to use only one in GROUP BY and rest of with aggregate function as like below:

SELECT 
    MAX(a.first_name) AS first_name, 
    MAX(a.last_name) AS last_name 
FROM actor a
INNER JOIN film_actor  fa  ON a.actor_id = fa.actor_id
GROUP BY a.actor_id
HAVING COUNT(fa.film_id) > 30

Upvotes: 0

Nabstar
Nabstar

Reputation: 43

use joined query instead try this

SELECT first_name, last_name
FROM actor a
JOIN actor_film af
ON(a.actor_id = af.actor_id)
WHERE (SELECT count(af.film_id) FROM af) > 30

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

Behaviour explanation of your current query

Your current query runs without any connection between actor and film_actor tables, so the results would be odd and would actually show every actor from the actor table if the number of rows in entire film_actor table with non-null value actor_id is less than 30.

Right approach for the task

Use INNER JOIN with grouping and having clause to only show actors who participated in at least 30 movies.

If a pair of actor_id, film_id in your table film_actor is unique then this query would suffice:

SELECT a.actor_id,a.first_name, a.last_name
FROM actor a
INNER JOIN film_actor fa ON
  a.actor_id = fa.actor_id
GROUP BY a.actor_id, a.first_name, a.last_name
HAVING COUNT(fa.film_id) > 30

However, if you are storing several roles an actor could play in a single movie, so that the pair mentioned earlier is not unique, then add distinct to the having clause:

HAVING COUNT(DISTINCT fa.film_id) > 30

Note that I've added actor_id to select and group by clause since two actors can have the same first and last name and you'd probably want to see those cases clearly.

Upvotes: 1

Code Different
Code Different

Reputation: 93151

Try this:

SELECT      a.first_name, a.last_name
FROM        actor       a
INNER JOIN  film_actor  fa  ON a.actor_id = fa.actor_id
GROUP BY    a.actor_id, a.first_name, a.last_name
HAVING      COUNT(fa.film_id) > 30

Upvotes: 4

Related Questions