Generates summary values that appear as additional rows in the query results.
start_of_select_statement compute row_aggregate (column_name) [, row_aggregate(column_name)]... [by column_name [, column_name]...]
is one of the following:
Function |
Meaning |
|---|---|
sum |
Total of values in the (numeric) column |
avg |
Average of values in the (numeric) column |
min |
Lowest value in the column |
max |
Highest value in the column |
count |
Number of values in the column |
is the name of a column. It must be enclosed in parentheses. Only numeric columns can be used with sum and avg.
One compute clause can apply several aggregate functions to the same set of grouping columns (see Examples 2 and 3). To create more than one group, use more than one compute clause (see Example 5).
calculates the row aggregate values 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.
Calculates the sum of the prices of each type of cook book that costs more than $12:
select type, price from titles where price > $12 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 14.99 trad_cook 20.95 sum ------------ 35.94 (5 rows affected)
Calculates the sum of the prices and advances for each type of cook book that costs more than $12:
select type, price, advance from titles where price > $12 and type like "%cook" order by type, price compute sum(price), sum(advance) by type
type price advance --------- --------- ------------ mod_cook 19.99 0.00 sum sum --------- ------------ 19.99 0.00
type price advance --------- --------- ------------ trad_cook 14.99 8,000.00 trad_cook 20.95 7,000.00 sum sum --------- ------------ 35.94 15,000.00 (5 rows affected)
Calculates the sum of the prices and maximum advances of each type of cook book that costs more than $12:
select type, price, advance from titles where price > $12 and type like "%cook" order by type, price compute sum(price), max(advance) by type
type price advance --------- --------- ------------- mod_cook 19.99 0.00 sum --------- 19.99 max ------------- 0.00
type price advance --------- --------- ------------- trad_cook 14.99 8,000.00 trad_cook 20.95 7,000.00 sum --------- 35.94 max ------------- 8,000.00 (5 rows affected)
Breaks on type and pub_id and calculates the sum of the prices of psychology books by a combination of type and publisher ID:
select type, pub_id, price from titles where price > $10 and type = "psychology" order by type, pub_id, price compute sum(price) by type, pub_id
type pub_id price ------------ --------- ----------- psychology 0736 10.95 psychology 0736 19.99 sum --------- 30.94
type pub_id price ------------ --------- --------- psychology 0877 21.59 sum --------- 21.59 (5 rows affected)
Calculates the grand total of the prices of psychology books that cost more than $10 in addition to calculating sums by type and pub_id:
select type, pub_id, price from titles where price > $10 and type = "psychology" order by type, pub_id, price compute sum(price) by type, pub_id compute sum(price) by type
type pub_id price ------------ --------- --------- psychology 0736 10.95 psychology 0736 19.99 sum --------- 30.94
type pub_id price ------------ --------- --------- psychology 0877 21.59 sum --------- 21.59 sum --------- 52.53 (6 rows affected)
Calculates the grand totals of the prices and advances of cook books that cost more than $10:
select type, price, advance from titles where price > $10 and type like "%cook" compute sum(price), sum(advance)
type price advance --------- ----------- -------------- mod_cook 19.99 0.00 trad_cook 20.95 8,000.00 trad_cook 11.95 4,000.00 trad_cook 14.99 7,000.00 sum sum ----------- -------------- 67.88 19,000.00 (5 rows affected)
Calculates the sum of the price of cook books and the sum of the price used in an expression:
select type, price, price*2 from titles where type like "%cook" compute sum(price), sum(price*2)
type price ------------ -------------- ------------ mod_cook 19.99 39.98 mod_cook 2.99 5.98 trad_cook 20.95 41.90 trad_cook 11.95 23.90 trad_cook 14.99 29.98 sum sum ============= ============ 70.87 141.74
The compute clause allows you to see the detail and summary rows in one set of results. You can calculate summary values for subgroups, and you can calculate more than one aggregate for the same group.
compute 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. See Example 6.
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 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 cannot use more than 127 aggregate columns in a compute clause.
You cannot use a compute clause in a cursor declaration.
Summary values can be computed for both expressions and columns. Any expression or column that appears in the compute clause must appear in the select list.
Aliases for column names are not allowed as arguments to the row aggregate in a compute clause, although they can be used in the select list, the order by clause, and the by clause of compute.
You cannot use select into in the same statement as a compute clause, because statements that include compute do not generate normal tables.
If a compute clause includes a group by clause:
The compute clause cannot contain more than 255 aggregates
The group by clause cannot contain more than 255 columns
Columns included in a compute clause cannot be longer than 255 bytes.
The aggregate functions 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. For example:
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)
The compute clause makes it possible to retrieve detail and summary rows with one command. For example:
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 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)
Table 1-4 lists the output and grouping of different types of compute clauses.
Clauses and grouping |
Output |
Examples |
|---|---|---|
One compute clause, same function |
One detail row |
1, 2, 4, 6, 7 |
One compute clause, different functions |
One detail row per type of function |
3 |
More than one compute clause, same grouping columns |
One detail row per compute clause; detail rows together in the output |
Same results as having one compute clause with different functions |
More than one compute clause, different grouping columns |
One detail row per compute clause; detail rows in different places, depending on the grouping |
5 |
If your server has a case-insensitive sort order installed, compute ignores the case of the data in the columns you specify. For example, given this data:
select * from groupdemo
lname amount ---------- ------------------ Smith 10.00 smith 5.00 SMITH 7.00 Levi 9.00 Lévi 20.00
compute by on lname produces these results:
select lname, amount from groupdemo order by lname compute sum(amount) by lname
lname amount ---------- ------------------------ Levi 9.00 Compute Result: ------------------------ 9.00 lname amount ---------- ------------------------ Lévi 20.00 Compute Result: ------------------------ 20.00 lname amount ---------- ------------------------ smith 5.00 SMITH 7.00 Smith 10.00 Compute Result: ------------------------ 22.00
The same query on a case- and accent-insensitive server produces these results:
lname amount ---------- ------------------------ Levi 9.00 Lévi 20.00 Compute Result: ------------------------ 29.00 lname amount ---------- ------------------------ smith 5.00 SMITH 7.00 Smith 10.00 Compute Result: ------------------------ 22.00
ANSI SQL – Compliance level: Transact-SQL extension.
Commands group by and having clauses, select
