Joins and Aggregation in View Definitions
When defining a non‑updatable view, you can specify one or more aggregate operators (SUM, AVG, MAX, MIN, and COUNT) in the SELECT expression list or in the conditional expression of a HAVING clause. Creating views with aggregation can eliminate the need to create and delete temporary tables. For many applications, an aggregate join index might be a more optimal solution. See “Aggregate Join Indexes” on page 369, “Optimizer Rules for Using Aggregate Join Indexes in a Query Plan” on page 369, and the chapter on hash and join indexes in Database Design for more information about aggregate join indexes.
Aggregate operators cannot be used in the conditional expression of a WHERE clause.