Count multiple items in the same column pulling data from 2 tables - mysql

Question

I have 2 tables and I need to count the number of times any role shows up in each movie

Example of Tables:

Movie Table

TITLE           MOVIEID     OTHER STUFF....
-------------   -------
MOVIE TITLE 1   ID# 1
MOVIE TITLE 2   ID# 2
MOVIE TITLE 3   ID# 3
ETC...

Role Table

ROLEID      MOVIEID     OTHER STUFF....
-------     -------
ROLE #1     ID# 1
ROLE #2     ID# 1
ROLE #3     ID# 2
ROLE #4     ID# 2
ROLE #5     ID# 3
ROLE #6     ID# 4
ROLE #7     ID# 5
ROLE #8     ID# 3
ETC....

I would like to get an output that looks like this:

TITLE           NUMROLES
-------------   --------------------------------------
MOVIE TITLE 1   COUNT (ROLE.MOVIEID) FOR MOVIE TITLE 1
MOVIE TITLE 2   COUNT (ROLE.MOVIEID) FOR MOVIE TITLE 2
MOVIE TITLE 3   COUNT (ROLE.MOVIEID) FOR MOVIE TITLE 3
MOVIE TITLE 4   COUNT (ROLE.MOVIEID) FOR MOVIE TITLE 4
MOVIE TITLE 5   COUNT (ROLE.MOVIEID) FOR MOVIE TITLE 5
ETC...

I have tried a several different things but I seem to always get a total count of all the roles instead of them being counted individually.

My "best" output Example:

mysql> SELECT TITLE, COUNT(ROLE.MOVIEID) AS NUMROLES
    -> FROM MOVIE, ROLE
    -> GROUP BY TITLE;
+-----------------------------+----------+
| TITLE                       | NUMROLES |
+-----------------------------+----------+
| Amadeus                     |      138 |
| Apollo 13                   |      138 |
| Batman                      |      138 |
| Batman & Robin              |      138 |
| Batman Begins               |      138 |
| Batman Forever              |      138 |
| Batman Returns              |      138 |
| Casablanca                  |      138 |
| Dirty Harry                 |      138 |
| Few Good Men, A             |      138 |
| Field of Dreams             |      138 |
| Fly, The                    |      138 |
| Forrest Gump                |      138 |
| Godfather, The              |      138 |
| Gone with the Wind          |      138 |
| Jerry Maguire               |      138 |
| Mrs. Doubtfire              |      138 |
| Naked City, The             |      138 |
| Sixth Sense, The            |      138 |
| Sudden Impact               |      138 |
| Terminator 2:  Judgment Day |      138 |
| Terminator, The             |      138 |
| Tootsie                     |      138 |
| Wizard of Oz, The           |      138 |
+-----------------------------+----------+
24 rows in set (0.02 sec)

I am thinking I don't have the proper knowledge of what I should be using in this case, can anyone point me in the right direction without just giving me the answer??

Bonus:

I also need to filter out any titles that don't have at least 6 for count of (ROLE.MOVIEID) - I am thinking I need to use HAVING for that.

Answer 1

Your Query:

SELECT TITLE, COUNT(ROLE.MOVIEID) AS NUMROLES
FROM MOVIE, ROLE
GROUP BY TITLE;

Here, you're doing Cross Join between tables MOVIE & ROLE. Which leads you to wrong result.

What you need here is Inner Join of these 2 tables based on the common column MOVIEID.

Following query should work:

SELECT m.TITLE, COUNT(r.MOVIEID) AS NUMROLES
FROM MOVIE m
inner join ROLE r
on m.MOVIEID = r.MOVIEID
GROUP BY m.TITLE
having NUMROLES >=6;

having NUMROLES >=6 is used to filter out TITLE with 5 or less than 5 counts.

You can learn more about JOIN by clicking here.

Answer 2

You're really close, just need to tweak your JOIN (it is missing a predicate), and add your HAVING for your bonus:

SELECT TITLE, COUNT(*) AS NUMROLES
FROM MOVIE M
JOIN ROLE R ON M.MOVIEID = R.MOVIEID --This is the join predicate (condition)
GROUP BY TITLE
HAVING COUNT(*) > 5;

This is explicit JOIN syntax. It's best practice to use this as the implicit variant (ie. commas in your FROM clause) is long since depreciated and harder to use anyway.

Note that you can use COUNT(r.movieID) if you want, but COUNT(*) will count the rows in the same fashion.