Groups With Conditions: HAVING Clause

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:

• When HAVING is used, it always follows a GROUP BY clause.

• When referring to aggregate values in the HAVING and ORDER BY clauses of a GROUP BY you must assign an alternative name to the field, and use that in the HAVING and ORDER BY clauses.

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.