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

Question

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  

Answer 1

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:

0. 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".

1. 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.

2. 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.

3. 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.

Answer 2

= is an equality test. However, NULL always fails.

... = ( SELECT ... ) -- This is an example of using a subquery in place of an expression. No problem. (Well, the subquery must return exactly 1 row with 1 column.)

IN (1,2,3) or `IN ('abc','def') -- These involve a list of literals.

IN ( SELECT ... ) -- This requires the SELECT to return any number of rows, but only 1 column.

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

requires you tie the things together, which your attempts failed to do.

First, you need an Actor table, then ActorMovie:

ActorMovie needs 2 columns: (actor_id, movie_id)

Meanwhile, let's add a PRIMARY KEY to each table. Let's call it (by convention) id.

Now let's fetch the data, then talk about using =:

SELECT a.name,
       ( SELECT COUNT(*) FROM movies AS m
            WHERE  m.id = am.movie_id
              AND  m.genre = 'comedy' )  As comedy_ct,
       ( SELECT COUNT(*) FROM movies AS m
            WHERE  m.id = am.movie_id
              AND  m.genre = 'tragedy' )  As tragedy_ct,
    FROM ActorMovie AS am
    JOIN Actor ON a.id = am.actor_id;

Now let's move those subqueries to the WHERE:

SELECT a.name
    FROM ActorMovie AS am
    JOIN Actor ON a.id = am.actor_id;
    WHERE ( SELECT ... 'comedy' ) =
          ( SELECT ... 'tragedy' );

(Yes, VIEWs could get involved, but I think it just makes this query more complex.)

ActorMovie is a "many-to-many" table relating many actors to many movies.

The id is needed in Actor and Movie because there can be multiple actors and movies with the same name.

This 3-table pattern is quite common. (Your question is not common.)

ActorMovie only has ids; it needs to JOIN to Actor to find the actor's name. Similarly, it needs to JOIN to Movie to find the genre.

You could have a Person table instead of Actor and add a 3rd column rel that says whether the person is an actor/director/producer/grip/accountant/flunkie/waterboy/etc.

Movie needs a release_year to help distinguish 'remakes'.

Answer 3

Obviously you can replace the CTE with some form of View. Just a novel use of a hashing function to identify genres and counts to tie the actors together in the genre.

CREATE TABLE #ActorMovie (
    a_name VARCHAR(100),
    a_YofB INT,
    m_title VARCHAR(100),
    m_year INT
);


CREATE TABLE #Movie (
    title VARCHAR(100),
    year INT,
    genre VARCHAR(50),
    budget DECIMAL(15,2),
    cost DECIMAL(15,2),
    gross_earnings DECIMAL(15,2)
);



INSERT INTO #Movie (title, year, genre, budget, cost, gross_earnings) VALUES
('Iron Man', 2008, 'Action', 140000000, 100000000, 585000000),
('Avengers: Endgame', 2019, 'Action', 356000000, 200000000, 2797501328),
('Forrest Gump', 1994, 'Drama', 55000000, 30000000, 678200000),
('Harry Potter and the Sorcerer''s Stone', 2001, 'Fantasy', 125000000, 90000000, 974755371);


INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES ('Robert Downey Jr.', 1965, 'Iron Man', 2008)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Scarlett Johansson', 1984, 'Iron Man', 2008)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Tom Hanks', 1956, 'Forrest Gump', 1994)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Emma Watson', 1990, 'Harry Potter and the Sorcerer''s Stone', 2001)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Leonardo DiCaprio', 1974, 'Inception', 2010)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Scarlett Johansson', 1984, 'Avengers: Endgame', 2019)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Robert Downey Jr.', 1965, 'Avengers: Endgame', 2019)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Jennifer Lawrence', 1990, 'The Hunger Games', 2012)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Brad Pitt', 1963, 'Fight Club', 1999)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Johnny Depp', 1963, 'Pirates of the Caribbean', 2003)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Keira Knightley', 1985, 'Pirates of the Caribbean', 2003)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Orlando Bloom', 1977, 'Pirates of the Caribbean', 2003)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Natalie Portman', 1981, 'Thor', 2011)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Chris Hemsworth', 1983, 'Thor', 2011)

;with actor_move as 
(select a_name, genre, count(*) as count_, LOWER(CONVERT(VARCHAR(32), 
HashBytes('MD5', CONVERT(varchar(4000), genre  + convert(varchar(1000), count(*)) )),2)) as id
 from #ActorMovie as am join #Movie as m on m.title = am.m_title and m.[year] = am.m_year
group by a_name, genre)


;with actor_move as 
(select a_name, genre, count(*) as count_, LOWER(CONVERT(VARCHAR(32), 
HashBytes('MD5', CONVERT(varchar(4000), genre  + convert(varchar(1000), count(*)) )),2)) as id
 from #ActorMovie as am join #Movie as m on m.title = am.m_title and m.[year] = am.m_year
group by a_name, genre)

select * into #actorgenre from actor_move; -- can filter on genres here

select a_name from #actorgenre where id in 
(select id from #actorgenre
group by id having count(id) > 1)
group by a_name having count(a_name) > 1
-- or filter on genres here


drop table #ActorMovie
drop table #Movie
drop table #actorgenre

Answer 4

You can do a query that counts the number of comedies of each actor, then another query counting the number of tragedies of each actor, then join those queries, like this:

CREATE TABLE #ActorMovie (
    a_name VARCHAR(100),
    a_YofB INT,
    m_title VARCHAR(100),
    m_year INT
);


CREATE TABLE #Movie (
    title VARCHAR(100),
    year INT,
    genre VARCHAR(50),
    budget DECIMAL(15,2),
    cost DECIMAL(15,2),
    gross_earnings DECIMAL(15,2)
);



INSERT INTO #Movie (title, year, genre, budget, cost, gross_earnings) VALUES
('Iron Man', 2008, 'Comedy', 140000000, 100000000, 585000000),
('Avengers: Endgame', 2019, 'Tragedy', 356000000, 200000000, 2797501328),
('Forrest Gump', 1994, 'Comedy', 55000000, 30000000, 678200000),
('Harry Potter and the Sorcerer''s Stone', 2001, 'Fantasy', 125000000, 90000000, 974755371);


INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES ('Robert Downey Jr.', 1965, 'Iron Man', 2008)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Scarlett Johansson', 1984, 'Iron Man', 2008)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Tom Hanks', 1956, 'Forrest Gump', 1994)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Emma Watson', 1990, 'Harry Potter and the Sorcerer''s Stone', 2001)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Leonardo DiCaprio', 1974, 'Inception', 2010)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Scarlett Johansson', 1984, 'Avengers: Endgame', 2019)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Robert Downey Jr.', 1965, 'Avengers: Endgame', 2019)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Jennifer Lawrence', 1990, 'The Hunger Games', 2012)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Brad Pitt', 1963, 'Fight Club', 1999)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Johnny Depp', 1963, 'Pirates of the Caribbean', 2003)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Keira Knightley', 1985, 'Pirates of the Caribbean', 2003)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Orlando Bloom', 1977, 'Pirates of the Caribbean', 2003)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Natalie Portman', 1981, 'Thor', 2011)
INSERT INTO #ActorMovie (a_name, a_YofB, m_title, m_year) VALUES('Chris Hemsworth', 1983, 'Thor', 2011)

SELECT q1.a_name, q1.ComedyCount, q2.TragedyCount
FROM (
    SELECT am.a_name, COUNT(*) AS ComedyCount
    FROM #ActorMovie am INNER JOIN #Movie m ON am.m_title=M.title 
    WHERE m.genre='Comedy' GROUP BY am.a_name
) q1 INNER JOIN (
    SELECT am.a_name, COUNT(*) AS TragedyCount
    FROM #ActorMovie am INNER JOIN #Movie m ON am.m_title=M.title 
    WHERE m.genre='Tragedy' GROUP BY am.a_name
) q2 ON q2.a_name = q1.a_name 
WHERE q1.ComedyCount = q2.TragedyCount

drop table #ActorMovie
drop table #Movie

I'm using the sample data from betelgeuce's answer, with some modifications in the genres.

Answer 5

Maybe this approach seems too simple. Any reason it won't work?

Select A.a_name, A.a_YofB
from ActorMovie A
Left outer join Movie M on
  M.title = A.m_title and
  M.year = A.m_year and
  M.genre in ('comedies','tragedies')
Group by A.a_name, A.a_YofB
Having
  sum(case when M.genre = 'comedies' then 1 else 0 end) =
  sum(case when M.genre = 'tragedies' then 1 else 0 end)