How to write an SQL query where the count of 2 different attributes is the same?

I created two different tables and then decided to put them (=) to each other however, what I am confused about is whether (=) is allowed to be used like that. I tried using IN but I didn't know where to go from there either. Is what I did correct?

Consider a relational schema for storing information related to movies:
ActorMovie(a_name, a_YofB, m_title, m_year)
Movie(title, year, genre, budget, cost, gross_earnings)

Q: List the names of all actors who acted in an equal number of comedies and tragedies.

Create View V1 AS (
Select      name
    From        ActorMovie a, Movie m
Where       a.m_title = m.title AND a.m_year = m. year AND Genre = ‘Comedy’)

Create View V2 (
Select      name
    From        ActorMovie a, Movie m
Where       a.m_title = m.title AND a.m_year = m. year AND Genre = 
‘Tragedies’)

Select      COUNT (name) = (Select  Count (name) FROM   V2)
From        V1

Q: List the names of all actors who acted in an equal number of comedies and tragedies.

You can accomplish this with a single query a few different ways, no need for creating Views. One way is with a CTE like so:

WITH ActorMovieGenreCounts AS
(
    SELECT 
        a.name AS ActorName,
        m.genre AS MovieGenre,
        COUNT(*) AS MovieCount
    FROM ActorMovie a
    INNER JOIN Movie m
        ON a.m_title = m.title 
        AND a.m_year = m. yea
    WHERE m.genre IN ('Comedy', 'Tragedy')
    GROUP BY a.name, m.genre
)

SELECT AMGC1.ActorName
FROM ActorMovieGenreCounts AS AMGC1
INNER JOIN ActorMovieGenreCounts AS AMGC2
    ON AMGC1.ActorName = AMGC2.ActorName -- Same Actor
    AND AMGC1.MovieGenre <> AMGC2.MovieGenre -- Different Genre (one is Comedy the other is Tragedies)
WHERE AMGC1.MovieCount = AMGC2.MovieCount -- Same Movie Count between those Genres

Couple notes:

-
This query works by first grouping up the actor and movie data to get the count of how many movies each actor acted in, filtered down to only the Comedy and Tragedies genres. Then it joins those results to itself, for the same actor, but where the genres are different (i.e. one is Comedy and the other is Tragedies) to put that actor's movie counts of each genre, on the same row. Finally it filters out the rows where thoss counts are different, therefore leaving only the rows where that actor "acted in an equal number of comedies and tragedies".

-
Notice the used of JOINs instead of the comma syntax you had, which is dated. You should use JOIN syntax to relate tables together, generally, for improved readability.

-
As noted in the beginning, you don't need to create such specific view objects to solve this. A single ad-hoc query like the above can solve the problem.

-
In general, you should tag the specific database system and version that you're using, since not all features and syntax are the same across all of them.

StackExchange Database Administrators Q#333737, answer score: 6