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
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.
SELECT DEPT, AVG(SALARY) AS AVG_SALARY
GROUP BY DEPT
HAVING AVG_SALARY > 30000
ORDER BY AVG_SALARY DESC ;
The results are:
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.