Michael Liao Michael Liao - 14 days ago 6
SQL Question

mysql count group by having

I have this table:

Movies (ID, Genre)


A movie can have multiple genres, so an ID is not specific to a genre, it is a many to many relationship. I want a query to find the total number of movies which have at exactly 4 genres. The current query I have is

SELECT COUNT(*)
FROM Movies
GROUP BY ID
HAVING COUNT(Genre) = 4


However, this returns me a list of 4's instead of the total sum. How do I get the sum total sum instead of a list of
count(*)
?

Answer

One way would be to use a nested query:

SELECT count(*)
FROM (
   SELECT COUNT(Genre) AS count
   FROM movies
   GROUP BY ID
   HAVING (count = 4)
) AS x

The inner query gets all the movies that have exactly 4 genres, then outer query counts how many rows the inner query returned.

Comments