HAVING is an additional filter on Aggregate totals. You can control the number of rows returned with a Having clause based on aggregate totals.
HAVING works with Aggregate functions. It is an extra WHERE condition in SQL.
Syntax for HAVING
SELECT DEPT_NO, AVG(SALARY) FROM EMPLOYEE_TABLE WHERE DEPT_NO IN(10,20,30) GROUP BY (DEPT_NO) HAVING AVG(SALARY)>45000 ORDER BY 1;
WHERE vs HAVING
- The WHERE clause filters rows on a condition. The HAVING clause filters on Aggregate totals.
- The WHERE clause is a generic filter.
- HAVING clause is not generic. It is based on a certain value. Also, you can say literals.
Five top Aggregate functions
MIN – The Minimum Value. MAX – The Maximum Value. AVG – The Average of the Column Values. SUM – The Sum Total of the Column Values. COUNT – The Count of the Column Values.