mysql count group by having

Question

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

score 101 · Accepted Answer · answered Oct 22 '11 at 05:13

101

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.

answered Oct 22 '11 at 05:13

Marc B

356,200
43
426
500

Then how effectively to calculate percentage of movies that have exactly 4 genres over total count of all movies? – stuckedunderflow Nov 05 '22 at 15:19
@stuckedoverflow divide it by the number of rows? Instead of `SELECT COUNT(*) FROM...` at the beginning write `SELECT COUNT(*) / (SELECT COUNT(DISTINCT ID) FROM movies) FROM ...` – izogfif Jul 21 '23 at 12:10

score 7 · Answer 2 · answered Oct 22 '11 at 05:13

7

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

answered Oct 22 '11 at 05:13

Conrad Frix

51,984
12
96
155

score 4 · Answer 3 · edited Nov 21 '16 at 06:21

4

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

edited Nov 21 '16 at 06:21

Taha Paksu

15,371
2
44
78

answered Oct 22 '11 at 05:12

Michael Durrant

93,410
97
333
497

I updated it again (v1) to have an alias for the statment otherwise one gets `Every derived table must have its own alias` – Michael Durrant Oct 22 '11 at 05:24

score 2 · Answer 4 · answered Oct 22 '11 at 05:15

2

What about:

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

answered Oct 22 '11 at 05:15

imm

5,837
1
26
32

What is the last 'a' supposed to do ? – Eric VB Nov 02 '18 at 01:36
1

@EricVB It´s a column alias for the result. MySQL allows (yet) to omit the keyword `AS`. – Titus Feb 26 '19 at 10:19

mysql count group by having

4 Answers4

Linked