CODEWITHSUNDEEP
sql
user1300580
user1300580

Reputation: 419

SQL SELECT QUERY COUNT

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

Upvotes: 4

Views: 40222

Answers (6)

Chetan
Chetan

Reputation: 51

Try this ..

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

This query return number of actors who have three or more roles in the database.

Upvotes: 2

ipr101
ipr101

Reputation: 24236

Try:

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

Upvotes: 7

Apurv Gupta
Apurv Gupta

Reputation: 860

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

Upvotes: 1

kaj
kaj

Reputation: 5251

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

Upvotes: 9

mkk
mkk

Reputation: 7693

your second query is almost correct.

You need to group by actorId, because you want to count them. You have put movieId, which makes no sense in that case.

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

Upvotes: 1

Chetter Hummin
Chetter Hummin

Reputation: 6817

Please try the following

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

Upvotes: 2

Related Questions