Sometimes you may want to specify a condition that applies to groups
rather than to individual rows. For example, you might want a list of
departments where the average departmental salary is above $30,000.
To express such a query, the HAVING clause is used. This clause
specifies which groups should be selected and is used in combination
with the GROUP BY clause. The form of this clause is as follows:
[GROUP BY column-name1 [,column-name2] ...
[HAVING search-condition ]
Conditions in the HAVING clause are applied after groups are formed.
The search condition of the HAVING clause examines the grouped rows
and produces a row for each group where the search condition in the
HAVING clause is true. The clause is similar to the WHERE
clause,
except the HAVING clause applies to groups.
Example: Which departments have an average salary above $30,000? Order the results by average salary, with highest average salary appearing first.
The statement:
SELECT DEPT, AVG(SALARY) AS AVG_SALARY
FROM EMPLOYEE
GROUP BY DEPT
HAVING AVG_SALARY > 30000
ORDER BY AVG_SALARY DESC ;
Syntax Notes:
The results are:
DEPT AVG_SALARY
MGT 45000
FIN 42000
MKT 33500
In this query, the average salary for all departments is computed, but only the names of those departments having an average salary above $30,000 are displayed. Notice that Research and Development's average of $27,500 is not displayed, nor is the Library's average of $22,000.
The GROUP BY clause does not sort the results, thus the need for the ORDER BY clause. Finally, note that the ORDER BY clause must be placed after the GROUP BY and HAVING clauses.
This chapter has covered the computational capabilities of Texis. In the next chapter, you will learn how to develop more complex queries by using the join operation and the nesting of queries.