The difference between HAVING and WHERE clause in SQL.
When analysts use querying to pull their desired data, they often use the WHERE clause. The WHERE clause can filter rows that meet the specified conditions based on text and numeric values. This helps analysts to pull the target observations, get important statistics, and achieve their analysis objective.
However, the WHERE clause is not allowed to contain any aggregate function, including AVG( ), SUM( ), MAX( ), MIN( ), and so on.
HAVING, a function that filters query results based on aggregate function, can make up for the deficiency of the WHERE clause. It processes the result of a GROUP BY clause and returns groups that satisfy the criteria. The order of the clauses should be:
SELECT ……
FROM……
WHERE……
GROUP BY……
HAVING……
ORDER BY……
Analysts can switch the order of the GROUP BY and HAVING clause without influencing the result.
One alternative to use filter data using aggregate functions is to use a query with a subquery. We can put the aggregate function into a subquery/parentheses start with the clause SELECT followed by the aggregate function(s). The subquery should be added within the WHERE clause, as shown in the image below.
