Row Aggregate Functions

Row aggregate functions generate summary values that appear as additional rows in the query results (unlike the aggregate function results, which appear as new columns). They allow you to see detail and summary rows in one set of results. Row aggregate functions (SUM, AVG, MIN, MAX, and COUNT) are used in a SELECT statement with the COMPUTE clause. You can calculate summary values for subgroups, and you can calculate more than one aggregate function for the same group. COMPUTE and the row aggregate functions are Transact-SQL enhancements to standard SQL.

Syntax

COMPUTE row_aggregate [, row_aggregate...]
[BY expression [, expression]...]

where

row_aggregate

Specifies the row aggregate function.

These are the row aggregate functions:

Aggregate
function
Result

SUM(expression) Returns the total of the values in the numeric column.

AVG(expression) Returns the average of the values in the numeric column.

COUNT(expression) Returns the number of non-null values in the column.

MAX(expression) Returns the highest value in the column.

MIN(expression) Returns the lowest value in the column.

BY

Indicates that row aggregate function values are to be calculated for subgroups. Whenever the value of the BY item changes, row aggregate function 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 group level.

expression



Aggregate function	Result
SUM(expression)	Returns the total of the values in the numeric column.
AVG(expression)	Returns the average of the values in the numeric column.
COUNT(expression)	Returns the number of non-null values in the column.
MAX(expression)	Returns the highest value in the column.
MIN(expression)	Returns the lowest value in the column.

Follows the rules for expressions as defined in the Expressions topic; however, when used as a row aggregate function, an expression is usually the name of a column. The expression must be enclosed in parentheses. Only numeric expressions (expressions that result in a decimal, float, int, money, numeric, real, smallint, smallmoney, or tinyint datatype) can be used with SUM and AVG. One COMPUTE clause can apply the same function to several columns or several functions to one column. When using more than one function, use more than one COMPUTE clause.

Remarks

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

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

You can't use SELECT INTO in the same statement as a COMPUTE clause because statements that include COMPUTE generate tables that include the summary results, which are not stored in the database.

If you use COMPUTE BY, you must also use an ORDER BY clause. The columns listed after COMPUTE BY must be identical to or a subset of those listed after ORDER BY, and they must be in the same left-to-right order, start with the same expression, and not skip any expressions. For example, if the ORDER BY clause is:

ORDER BY a, b, c

The COMPUTE BY clause can be any or all of these:

COMPUTE BY a, b, c
COMPUTE BY a, b
COMPUTE BY a

You must use a column name or an expression in the ORDER BY clause.

The COMPUTE keyword can be used without BY to generate grand totals, grand counts, and so on. ORDER BY is optional if you use the COMPUTE keyword without BY.

When you sum or average integer data, SQL Server treats the result as an int value, even if the datatype of the column is smallint or tinyint.

Note To reduce the possibility of overflow errors in DB-Library programs, make all variable declarations for the results of averages or sums type int.

In a SELECT statement with a COMPUTE clause, the order of columns in the select list overrides the order of the aggregate functions in the COMPUTE clause. DB-Library programmers must be aware of this order requirement to put the aggregate function results in the correct place. For example:

SELECT a, b, c
FROM test
COMPUTE SUM(c), MAX(b), MIN(a)
a	b	c
---------	----------	----------
1	2	3
3	2	1

		sum
		=======
		4
	max
	=======
	2
min
=========
1

(3 rows affected)

Examples

A. Aggregates and Row Aggregates

The following two examples illustrate the differences between aggregate functions and row aggregate functions. The first shows aggregate functions giving summary data only, and the second shows row aggregate functions giving detail and summary data.

Aggregate Functions

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)

Row Aggregates

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	20.95	7,000.00
trad_cook	14.99	8,000.00
trad_cook	11.95	4,000.00
	sum	sum
	------------	------------
	47.89	19,000.00

(7 rows affected)

B. Group Totals with COMPUTE BY

This example calculates the sum of the all prices (for prices over $10) for each type of cookbook, in order first by type of book and then by price of book.

SELECT type, price
    FROM titles
        WHERE price > $10
        AND type LIKE '%cook'
            ORDER BY type, price
            COMPUTE SUM(price) BY type

type	price
-----------	----------
mod_cook	19.99
	sum
	----------
	19.99

type	price
------------	----------
trad_cook	11.95
trad_cook	14.99
trad_cook	20.95
	sum
	----------
	47.89

(6 rows affected)