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(*)
?
This question is related to
mysql
sql
group-by
aggregate-functions
SELECT COUNT(*)
FROM (SELECT COUNT(*)
FROM movies
GROUP BY id
HAVING COUNT(genre) = 4) t
What about:
SELECT COUNT(*) FROM (SELECT ID FROM Movies GROUP BY ID HAVING COUNT(Genre)=4) a
Maybe
SELECT count(*) FROM (
SELECT COUNT(*) FROM Movies GROUP BY ID HAVING count(Genre) = 4
) AS the_count_total
although that would not be the sum of all the movies, just how many have 4 genre's.
So maybe you want
SELECT sum(
SELECT COUNT(*) FROM Movies GROUP BY ID having Count(Genre) = 4
) as the_sum_total
Source: Stackoverflow.com