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:
column_name – is the name of a column. It 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 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.
