CODEWITHSUNDEEP
mysqlsqlgroup-byaggregate-functions
Michael Liao
Michael Liao

Reputation: 2663

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(*)?

Upvotes: 57

Views: 166738

Answers (4)

Michael Durrant
Michael Durrant

Reputation: 96544

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

Upvotes: 4

imm
imm

Reputation: 5919

What about:

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

Upvotes: 2

Marc B
Marc B

Reputation: 360762

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.

Upvotes: 101

Conrad Frix
Conrad Frix

Reputation: 52675

SELECT COUNT(*) 
FROM   (SELECT COUNT(*) 
        FROM   movies 
        GROUP  BY id 
        HAVING COUNT(genre) = 4) t

Upvotes: 7

Related Questions