Can a WHERE clause be applied with a HAVING statement in the same query?


#1

Question

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;