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.
COMPUTE row_aggregate [, row_aggregate...]
[BY expression [, expression]...]
where
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. |
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) |
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.
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 |
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) |
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) |