Aggregates Without group by

By definition, scalar aggregates apply to all rows in a table, producing a single value for the entire table for each function.

This Transact-SQL extension 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 SAP ASE 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 multitable joins.