When an aggregate function is used in a select statement that does not include a group by clause, it produces a single value. The query can operate on all rows in a table or on a subset of the rows defined by a where clause.
When an aggregate function produces a single value, the function is called a scalar aggregate, or an ungrouped aggregate. Here is showplan output for an ungrouped aggregate:
select avg(advance) from titles where type = "business"
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.
Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.
FROM TABLE
titles
Nested iteration.
Index : type_price
Forward scan.
Positioning by key.
Keys are:
type ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
STEP 2
The type of query is SELECT.
This is a two-step query, similar to the showplan from the group by query shown earlier.
Since the scalar aggregate returns a single value, Adaptive Server uses an internal variable to compute the result of the aggregate function, as the qualifying rows from the table are evaluated. After all rows from the table have been evaluated (step 1), the final value from the variable is selected (step 2) to return the scalar aggregate result.
