InterBase Quick Start: Part IV - Using the HAVING Clause

From InterBase
Jump to: navigation, search

Go Up to InterBase Quick Start: Part IV - Grouping and Ordering Query Results


Just as a WHERE clause reduces the number of rows returned by a SELECT clause, the HAVING clause can be used to reduce the number of rows returned by a GROUP BY clause. Like the WHERE clause, a HAVING clause has a search condition. However, in a HAVING clause, the search condition typically corresponds to an aggregate function used in the SELECT clause.

Image 025.jpgControlling your Query with GROUP BY and HAVING

Issue the following query to list the departments that have an average budget of over 60,000 and order the result set by department.

SELECT department,
       AVG(budget)
FROM   Department
GROUP  BY department
HAVING AVG(budget) > 60000
ORDER  BY department

The result set looks like this:

TutorialGrouping4.png

Advance To: