SQL: How to SUM and AVG on a left/right JOIN-CodePudding

With the table above, I basically I want to be able to return an array of

[average minutes, genre, number of movies of that genre]

[[86, "Horror", 34]......[66, "Comedy", 32]]

So far I have coded like this but it doesn't work

SELECT AVG(movies.minutes), genres.name, COUNT(movies.name)
FROM movies
LEFT JOIN genres
ON genres.id = movies.genre_id

It returns the average of all movies, a single genre and all the movies like

[82, "Horror, 223]

CodePudding user response：

You cannot use aggregation functions without grouping them. Try adding a GROUP BY clause in your statement.

CodePudding user response：

You simply need to add the GROUP BY genres.name at the end of your query. Therefore, your query should be

SELECT AVG(movies.minutes), genres.name, COUNT(movies.name)
FROM movies
LEFT JOIN genres
   ON genres.id = movies.genre_id
GROUP BY genres.name

You may ask why your query without GROUP BY genres.name works and returns 'horror'. The answer is that 'horror' returned by the query is picked by random and tomorrow you may get 'comedy'.