The article explains how to restrict GROUP BY results with the use of HAVING clause.
1. HAVING Clause
GROUP BY clause groups the query result set and produces aggregated results within the group like count, sum, maximum and minimum. For cases, where these results have to be restricted or filtered, it can’t be done through WHERE clause. Filtering of Grouped Results is done by HAVING clause. This clause is exclusively used to filter grouped results. Therefore, for a HAVING clause, the query result must be grouped.
Code sql:
SQL> SELECT DEPARTMENT_ID, SUM(SALARY) FROM EMPLOYEE WHERE SUM(SALARY) > 5000; WHERE SUM(SALARY) > 5000 * ERROR at line 3: ORA-00934: GROUP FUNCTION IS NOT allowed here
It follows the Group by clause in the SELECT statement. But it can also precede the Group by clause (not logical and not recommended).
Syntax
Code :
SELECT <column list>, <group by function> FROM <table name> WHERE <conditions> GROUP BY <column list> HAVING <group by function condition>
Examples:
Example 1. Below SQL query shows the department and sum of salaries in the department, if it exceeds 10000.
Code sql:
SQL> SELECT JOB_ID, SUM (SALARY) FROM EMPLOYEE GROUP BY JOB_ID HAVING SUM (SALARY) > 10000; JOB_ID SUM(SALARY) -------- ------------------- MGR 12000 DEV 23500 HR 14000 ADM 17000
Example 2. Below SQL query shows departments with more than 50 employees.
Code sql:
SQL>SELECT DEPARTMENT_ID, COUNT (*) FROM EMPLOYEE GROUP BY DEPARTMENT_ID HAVING COUNT (*) > 50
Result of above query is depicted with the help of below figure.
Example 3. Below SQL query shows the departments where total salary is more than the average salary of that department.
Code sql:
SQL>SELECT DEPARTMENT_ID, SUM (SALARY), AVG (SALARY) FROM EMPLOYEE GROUP BY DEPARTMENT_ID HAVING SUM (SALARY) > AVG(SALARY)
HAVING clause is a great filtering tool and plays crucial role in large reporting applications, where aggregated results are required for business forecasting and future insights.
References
