SQL SELECT QUERY COUNT

Question

I'm new to this so bear with me.

I'm trying to create a select query from a Movie database. Amongst other tables there is a Role table that features information such as roleID, roleName, gender, actorID, movieID. An actor can have many roles in different movies.

I am trying to create the query so it will tell me how many actors have three or more roles in the database.

I've tried a few solutions and they output data just not sure if it is right.

SELECT COUNT (DISTINCT actorID) FROM Role WHERE actorID >= 3

SELECT COUNT (actorID) FROM Role GROUP BY movieID HAVING COUNT (actorID) >=3

Answer 1

Try something like:

select actorID, count(*)
from Roles  
group by actorID  
having count (*) >= 3

If you want to retrieve other properties about the actor you add those to both the select and group by clauses

Answer 2

Try:

SELECT COUNT(*) FROM Role 
GROUP BY actorid
HAVING COUNT(*) >= 3