Clever Geek Handbook

SQL SELECT QUERY COUNT

I'm new to this, so bear with me.

I am trying to create a select query from a database Movie

. Among other tables, there is a table Role

that contains information such as roleID, roleName, gender, actorID, movieID

. An actor can have many roles in different films.

I am trying to create a query to tell me how many members have three or more roles in the database.

I've tried several solutions and they are outputting data just not sure if they are correct.

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

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


source to share


6 answers


Try something like:

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


If you want to get other properties about an actor, you add them both to the selection clauses and by groups

+9


source


Try:



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


source


Try the following

SELECT actorID, COUNT(actorID) 
FROM Role 
GROUP BY actorID 
HAVING COUNT (actorID) >=3
+2


source


Try it.

SELECT COUNT(*) 
FROM (SELECT actorID FROM Roles GROUP BY actorID HAVING COUNT(*)>=3) AS actor;

This query returns the number of principals who have three or more roles in the database.

+2


source


your second query is almost correct.

You need to group the actorId because you want to count them. You put movieId, which doesn't make sense in this case.

SELECT COUNT (*) as actorRoles
FROM Role 
GROUP BY actorId
HAVING actorRoles >=3;
+1


source 


select   count(*) 
from     Role
where    actorID in 
         (
             select    actorID 
             from      Role 
             group by  actorID 
             having    count(actorID) > 3
         )
+1


source






More articles:


All Articles