CODEWITHSUNDEEP
sql
user1953825
user1953825

Reputation:

Having trouble figuring out how to count certain values in SQL

I have three tables:

ActorInfo(actorID, firstname, lastname)
CastInfo(movieID, actorID)
GenreInfo(movieID, genre)

I'm trying to write an SQL query that will return the details of an actor, along with the number of 'Drama' movies they've starred in, without any repetition.

So, ideally, I'd have something like,

1 George Clooney 10
2 Brad Pitt 8
3 Matt Damon 6

So, far my code is as follows:

SELECT actorinfo.actorID, actorinfo.firstname, actorinfo.lastname, COUNT(DISTINCT actorinfo.actorID) as total
FROM actorinfo
LEFT OUTER JOIN castinfo
ON actorinfo.actorID= castinfo.actorID
LEFT OUTER JOIN genreinfo
ON castinfo.mvID = genreinfo.mvID
WHERE genreinfo.genre = 'Drama'
GROUP BY actorinfo.actorID, actorinfo.firstname, actorinfo.lastname
ORDER BY total ASC;

However, this is just returning the result of 1 for 'total' for all actors. I've really hit a wall here, and I'm unsure how to solve this particular problem.

Upvotes: 0

Views: 105

Answers (4)

melc
melc

Reputation: 11671

Just remove the distinct on the column you are counting. 

SELECT actorinfo.actorID, 
actorinfo.firstname, 
actorinfo.lastname, 
COUNT(actorinfo.actorID) as total
FROM ActorInfo as actorinfo
LEFT OUTER JOIN CastInfo as castinfo
ON actorinfo.actorID= castinfo.actorID
LEFT OUTER JOIN GenreInfo as genreinfo
ON castinfo.mvID = genreinfo.mvID
WHERE genreinfo.genre = 'Drama'
GROUP BY actorinfo.actorID
ORDER BY total ASC;

http://sqlfiddle.com/#!2/29364/6

You don't want to count the distinct occurrences of the id value in your group, since the id you are grouping on will obviously always have the same value within the group. Alternatively you could group on the id of genreinfo and id of actor and count all records within group, a bit more self explanatory.

SELECT actorinfo.actorID, 
actorinfo.firstname, 
actorinfo.lastname, 
COUNT(*) as total
FROM ActorInfo as actorinfo
LEFT OUTER JOIN CastInfo as castinfo
ON actorinfo.actorID= castinfo.actorID
LEFT OUTER JOIN GenreInfo as genreinfo
ON castinfo.mvID = genreinfo.mvID
WHERE genreinfo.genre = 'Drama'
GROUP BY genreinfo.mvId,actorinfo.actorID
ORDER BY total ASC;

If you have the information related to specific movie , i suppose that would go to the castinfo where the relationship between actor and genre is held. In that case you should do the following to avoid repetition,

SELECT actorinfo.actorID, 
actorinfo.firstname, 
actorinfo.lastname, 
COUNT(DISTINCT castinfo.movie_name) as total
FROM ActorInfo as actorinfo
LEFT OUTER JOIN CastInfo as castinfo
ON actorinfo.actorID= castinfo.actorID
LEFT OUTER JOIN GenreInfo as genreinfo
ON castinfo.mvID = genreinfo.mvID
WHERE genreinfo.genre = 'Drama'
GROUP BY genreinfo.mvId,actorinfo.actorID
ORDER BY total ASC;

http://sqlfiddle.com/#!2/aa4363/1

Upvotes: 1

Kaf
Kaf

Reputation: 33809

For Sql-Server 2005 and above (Fiddle Demo): 

;With CTE as (
   Select c.actorID, count(distinct c.movieID) mCount
   From CastInfo c 
      join GenreInfo g on c.movieID = g.movieID
   Where g.genre = 'Drama'
   Group by c.actorID
)
Select a.actorID, a.firstName, a.lastName, coalesce(cte.mCount,0) total
from actorInfo a
      left join cte on a.actorID = cte.actorID
Order by total

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35583

Without detailed knowledge of your data model there is some guesswork. This should provide a reliable count of movies "without duplication". It might only be necessary however if an actor can be listed more than once against a single movie (e.g. if playing multiple roles in that movie).

SELECT
        actorinfo.actorID
      , actorinfo.firstname
      , actorinfo.lastname
      , COUNT(DISTINCT castinfo.mvID) AS total /* distinct of movie id's */
FROM ActorInfo AS actorinfo
LEFT OUTER JOIN CastInfo AS castinfo
        ON actorinfo.actorID = castinfo.actorID
LEFT OUTER JOIN GenreInfo AS genreinfo
        ON castinfo.mvID = genreinfo.mvID
WHERE genreinfo.genre = 'Drama'
GROUP BY 
        actorinfo.actorID
      , actorinfo.firstname
      , actorinfo.lastname
ORDER BY
        total ASC
      , actorinfo.firstname
      , actorinfo.lastname
;

Upvotes: 0

Sankar
Sankar

Reputation: 172

Try the query in the following way. Hope this works... 

SELECT actorinfo.actorID, actorinfo.firstname, actorinfo.lastname, COUNT(actorinfo.actorID) as total FROM actorinfo 
LEFT OUTER JOIN castinfo
ON actorinfo.actorID= castinfo.actorID
LEFT OUTER JOIN genreinfo
ON castinfo.mvID = genreinfo.mvID
WHERE genreinfo.genre = 'Drama'
GROUP BY actorinfo.actorID, actorinfo.firstname, actorinfo.lastname
ORDER BY total ASC;

Upvotes: 0

Related Questions