mysql count group by having

mysql sql group-by aggregate-functions
Michael Liao picture Michael Liao · Oct 22, 2011 · Viewed 135.3k times · Source

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

Marc B picture Marc B · Oct 22, 2011

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.

Related questions

MySQL "Group By" and "Order By"

I want to be able to select a bunch of rows from a table of e-mails and group them by the from sender. My query looks like this: SELECT `timestamp`, `fromEmail`, `subject` FROM `incomingEmails` GROUP BY LOWER(`fromEmail`) ORDER BY `…

Read More
SQL Query to get column values that correspond with MAX value of another column?

Ok, this is my query: SELECT video_category, video_url, video_date, video_title, short_description, MAX(video_id) FROM videos GROUP BY video_category When it pulls the data, I get the correct row for the video_id, but …

Read More
Retrieving the last record in each group - MySQL

There is a table messages that contains data as shown below: Id Name Other_Columns ------------------------- 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B B_data_1 5 B B_data_2 6 C C_data_1 If I run a query select * …

Read More