By definition, scalar aggregates apply to all rows in a table, producing a single value for the whole table for each function. The Transact-SQL extension that allows you to include extended columns with vector aggregates also allows you to include extended columns with scalar aggregates. For example, look at the publishers table:
pub_id pub_name city state ------ ------------------ -------------- ----- 0736 New Age Books Boston MA 0877 Binnet & Hardley Washington DC 1389 Algodata Infosystems Berkeley CA
It contains three rows. The following query produces a three-row scalar aggregate based on each row of the table:
select pub_id, count(pub_id) from publishers
pub_id ---------- --------- 0736 3 0877 3 1389 3 (3 rows affected)
Because Adaptive Server treats publishers as a single group, the scalar aggregate applies to the (single-group) table. The results display every row of the table for each column you include in the select list, in addition to the scalar aggregate.
The where clause behaves the same way for scalar aggregates as with vector aggregates. The where clause restricts the columns included in the aggregate summary values, but it does not affect the rows that appear in the results for each extended column you specify in the select list. For example:
select pub_id, count(pub_id) from publishers where pub_id < "1000"
pub_id -------------- ----------- 0736 2 0877 2 1389 2 (3 rows affected)
Like the other Transact-SQL extensions to group by, this extension provides results that may be difficult to understand, especially for queries on large tables or queries with multi-table joins.
