WHERE clause be applied with a
HAVING statement in the same query?
Yes, you can absolutely apply a
WHERE clause in a query that also utilizes a
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
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
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))
WHERE box_office > 500000
GROUP BY genre
HAVING COUNT(*) > 5;
This information really helps!
@jephos249 Thanks a lot …
why would COUNT (*) > 5 works , when * calls for all columns?
is it because the data is already restricted to ‘genre’ because of the ‘GROUP BY’ line?
Would 'HAVING COUNT (genre) > 5 provide the same result?
- 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.
@jephos249 it helped a lot
Does anyone know why " The total number of apps at each price point would be given by
COUNT(*) . "?
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.