database - Oracle SQL count and group by

Question

I am trying to show the details of movie, with number of roles and the number of genres it is classified as. (Family, Fantasy).

I expected the result to come out as:

movie_id   title              genre     count(movie_role) count(genre)  
675        harry potter a     family    3                 2
675        harry potter a     fantasy   3                 2
767        harry potter b     family    3                 1

My code:

SELECT movie_id, title, count(movie_role), genre
from moviesInGenre
group by movie_id, title, genre;

Because harry potter a is classified as genre family and fantasy, so I want it to have a column counting the genres it is classified as. (2). And harry potter b is classified as family only so should have a count of 1.

Sample data:

movie_id  title           movie_role   actor_id   aname   genre
675       harry potter a  Harry        10993      Jarney  Family
675       harry potter a  Nana         10232      Sam     Fantasy
767       harry potter b  John         10911      Cart    Family

Thanks in advance!

Answer 1

You could do this with the help of subqueries which find the various distinct counts:

SELECT
    t.movie_id,
    t.title,
    t.genre,
    rc.role_cnt,
    g.genre_cnt
FROM moviesInGenre t
INNER JOIN
(
    SELECT movie_id, COUNT(DISTINCT movie_role) AS role_cnt
    FROM moviesInGenre
    GROUP BY movie_id
) rc
    ON rc.movie_id = t.movie_id
INNER JOIN
(
    SELECT movie_id, COUNT(DISTINCT genre) AS genre_cnt
    FROM moviesInGenre
    GROUP BY movie_id
) g
    ON g.movie_id = t.movie_id;

Demo