Vector and scalar aggregates  Datatype conversion functions

Chapter 2: Transact-SQL Functions

Aggregate functions as row aggregates

Row aggregate functions generate summary values that appear as additional rows in the query results.

To use the aggregate functions as row aggregates, use the following syntax:

Start of select statement 
compute row_aggregate(column_name) 
		[, row_aggregate(column_name)]... 
	[by column_name [, column_name]...] 

Where:

The row aggregates make it possible to retrieve detail and summary rows with one command. The aggregate functions, on the other hand, ordinarily produce a single value for all the selected rows in the table or for each group, and these summary values are shown as new columns.

The following examples illustrate the differences:

select type, sum(price), sum(advance) 
from titles 
where type like "%cook" 
group by type 
type 
----------  ----------  ---------------- 
mod_cook         22.98         15,000.00 
trad_cook        47.89         19,000.00 
 
(2 rows affected) 
select type, price, advance 
from titles 
where type like "%cook" 
order by type 
compute sum(price), sum(advance) by type 
type        price       advance 
----------  ----------  ---------------- 
mod_cook          2.99         15,000.00 
mod_cook         19.99              0.00 
            sum         sum 
            ----------  ---------------- 
                 22.98         15,000.00 
type         price      advance 
----------  ----------  ---------------- 
trad_cook        11.95          4,000.00 
trad_cook        14.99          8,000.00 
trad_cook        20.95          7,000.00 
            sum         sum 
            ----------  ---------------- 
                 47.89         19,000.00 
(7 rows affected) 
type        price       advance 
----------  ----------  ---------------- 
mod_cook          2.99         15,000.00 
mod_cook         19.99              0.00 

Compute Result: 
---------------------- -----------------
                 22.98         15,000.00 
type         price         advance 
----------  ----------  ---------------- 
trad_cook        11.95          4,000.00 
trad_cook        14.99          8,000.00 
trad_cook        20.95          7,000.00 

Compute Result: 
---------------------- -----------------
                 47.89         19,000.00 
(7 rows affected) 

The columns in the compute clause must appear in the select list.

The order of columns in the select list overrides the order of the aggregates in the compute clause. For example:

create table t1 (a int, b int, c int null)
insert t1 values(1,5,8)
insert t1 values(2,6,9)
(1 row affected)
compute sum(c),  max(b), min(a)
select a, b, c from t1
a           b           c
----------- ----------- -----------
         1           5           8
         2           6           9

Compute Result:
----------- ----------- -----------
         1           6          17

If the ansinull option is set off (the default), there is no warning when a row aggregate encounters a null. If ansinull is set on, a query returns the following SQLSTATE warning when a row aggregate encounters a null:

Warning - null value eliminated in set function

You cannot use select into in the same statement as a compute clause because there is no way to store the compute clause output in the resulting table.





Copyright © 2005. Sybase Inc. All rights reserved. Datatype conversion functions

View this book as PDF