When an aggregate function is combined with group by, the result is called a grouped aggregate, or vector aggregate. The query results have one row for each value of the grouping column or columns.
The following example illustrates a grouped aggregate:
select type, avg(advance) from titles group by type
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT (into Worktable1).
GROUP BY
Evaluate Grouped COUNT AGGREGATE.
Evaluate Grouped SUM OR AVERAGE AGGREGATE.
FROM TABLE
titles
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.
STEP 2
The type of query is SELECT.
FROM TABLE
Worktable1.
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
In the first step, the worktable is created, and the aggregates are computed. The second step selects the results from the worktable.
