Aggregate Functions as Row Aggregates

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

To use the aggregate functions as row aggregates, use:

Start of select statement

compute row_aggregate(column_name) 
		[, row_aggregate(column_name)]... 
	[by column_name [, column_name]...]

where:

column_name – is the name of a column, which must be enclosed in parentheses. Only exact numeric, approximate numeric, and money columns can be used with sum and avg.

One compute clause can apply the same function to several columns. When using more than one function, use more than one compute clause.
by – indicates that row aggregate values are to be calculated for subgroups. Whenever the value of the by item changes, row aggregate values are generated. If you use by, you must use order by.

Listing more than one item after by breaks a group into subgroups, and applies a function at each level of grouping.

The row aggregates let you retrieve detail and summary rows with one command. The aggregate functions, however, 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.

These 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.