CODEWITHSUNDEEP
mysqlsql
Borovez
Borovez

Reputation: 79

SQL query aggregate multiple queries

I have a query that is like so: 

SELECT A.director, COUNT(*) as NumActors
FROM CanWorkWith A
GROUP BY A.director
HAVING COUNT(*) >= 5;

The CanWorkWith Table:

CanWorkWith(actor, director)

This query will return only the directors that have ATLEAST 5 actors that they can work with.

So, now I want to take the result of this query, and search another table to count the number of movies said director(s) have.

So, if the director has 5 actors, then I want to search for that director in a table called: 

Movies (title, year, director, budget, earnings)

And then finally, return the director, and the TOTAL count of Titles that director has, doing this for all the directors with more than 5 actors.

Thanks...

Upvotes: 2

Views: 290

Answers (1)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

Just do a JOIN between this result and the query that returns the number of movies per director.

Something like this:

SELECT 
    M.director, 
    COUNT(M.title) as 'Number of movies',
    D.NumActors
FROM Movies M
    INNER JOIN (
        SELECT A.director, COUNT(*) as NumActors
        FROM CanWorkWith A
        GROUP BY A.director
        HAVING COUNT(*) >= 5
   ) D ON M.director = D.director
GROUP BY M.director, 
         D.NumActors

Upvotes: 2

Related Questions