Can a WHERE clause be applied with a HAVING statement in the same query?
Answer
Yes, you can absolutely apply a WHERE clause in a query that also utilizes a HAVING statement.
When you apply a WHERE clause in the same query, it must always be before any GROUP BY, which in turn must be before any HAVING.
As a result, the data is essentially filtered on the WHERE condition first. Then, from this filtered data, it is grouped by specified columns and then further filtered based on the HAVING condition.
Example
/*
This will first filter the movies with a box_office > 500000.
Then, it will group those results by genre, and finally restrict
the query to genres that have more than 5 movies.
*/
SELECT genre, ROUND(AVG(score))
FROM movies
WHERE box_office > 500000
GROUP BY genre
HAVING COUNT(*) > 5;
From my point of view, âCOUNT(*)â is properly working especially because of its asterisk.
Because COUNT only shows us rows with not empty values. And if in some column there had been an empty value, and we used that column as an argument to COUNT (as in âCOUNT(some_column)â), then that row wouldnât have been counted and our result would have been spoiled. But with asterisk we would check all the columns for values. And add +1 for the whole row if we find some.
Yes, also it is working because of our use of grouping.
Without âGROUP BYâ, in output there would be only one row with some not expected values.
Yes, as iâve mentioned before, it would provide the same result only if in the âgenreâ column there wouldnât be any empty values in the cells. Otherwise, the program will just not see those rows.
COUNT just counts the number of rows, since youâve grouped them up already youâre simply counting the number of rows at each grouped price point, giving you the total number of apps at each price point.
Just to make sure I understand - a genre with a movie of less than 500,000 would not be counted toward the at least 5 movies per genre right? becaue it already filtered those out from the where statement. Thank you!
@dancemaster thatâs correct. The order as they said in the OP goes WHERE, GROUP BY, HAVING. Any rows filtered out by WHERE will not be included in the aggregate functions performed through GROUP BY.
