CODEWITHSUNDEEP
mysqlsqlinner-join
Josh Friedlander
Josh Friedlander

Reputation: 11657

SQL Group by Count of Primary Key

I'm combining two tables with an inner join where the Primary ID is the movie ID, and I'd like to group them by number of movies per actor per year. However - possibly because the movie ID is the primary key - the results that I'm getting aren't correct. Below is my code. The DB is from iMDB, it has a cast table with movies and actors, an actors table and a movies table with the year. 

SELECT 
                c.actor_id, m.year, COUNT(m.title)
            FROM
                cast c
            INNER JOIN movies m
            WHERE
                c.movie_id = m.id
            GROUP BY m.year
            ORDER BY COUNT(m.title) DESC;

Upvotes: 0

Views: 2213

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726699

Everything not aggregated must be in GROUP BY. Most RDBMSs would error out on your SQL, but MySQL would give you "a free pass" on it:

SELECT 
    c.actor_id, m.year, COUNT(m.title)
FROM
    cast c
INNER JOIN actor_movie am ON am.actor_id=am.actor_id
INNER JOIN movies m ON am.movie_id=m.movie_id
WHERE
    c.movie_id = m.id
GROUP BY c.actor_id, m.year
ORDER BY COUNT(m.title) DESC;

Note that inner join needs an ON clause which you did not include. I added one possible implementation with an actor-movie table that I suppose you have (or should have).

Upvotes: 1

kc2018
kc2018

Reputation: 1460

Need to include actor_id in the GROUP clause cause you are counting number of movies per actor per year.

SELECT 
                c.actor_id, m.year, COUNT(m.title)
            FROM
                cast c
            INNER JOIN movies m
            WHERE
                c.movie_id = m.id
            GROUP BY c.actor_id, m.year
            ORDER BY COUNT(m.title) DESC;

Upvotes: 2

Related Questions