Menu
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(*)?
404
SELECT DISTINCT ColumnName FROM TableName; Using the COUNT() function with the GROUP BY clause, then the query will produce the number of values as the count for each subgroup created based on the table column values or expressions.
While the GROUP BY Clause groups rows that have the same values into summary rows. The having clause is used with the where clause in order to find rows with certain conditions. The having clause is always used after the group By clause.
After using GROUP BY to filter records with aggregate functions like COUNT, use the HAVING clause. It's always used after the GROUP BY clause. In HAVING, we use a condition to compare a value with one returned by the aggregate function. In the example, we compare whether COUNT(id) returns a value higher than two.
Both GROUP BY and ORDER BY are clauses (or statements) that serve similar functions; that is to sort query results. However, each of these serve very different purposes; so different in fact, that they can be employed separately or together.
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.
164
SELECT COUNT(*) FROM (SELECT COUNT(*) FROM movies GROUP BY id HAVING COUNT(genre) = 4) t If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
