These summary values appear as additional rows in the query results, unlike the aggregate results of a group by clause, which appear as new columns.
A compute clause allows you to see detail and summary rows with one select statement. You can calculate summary values for subgroups and you can calculate more than one row aggregate (see "Row aggregates and compute") for the same group.
The general syntax for compute is:
compute row_aggregate(column_name)The row aggregates you can use with compute are sum, avg, min, max, and count. You can use sum and avg only with numeric columns. Unlike the order by clause, you cannot use the positional number of a column from the select list instead of the column name.
[, row_aggregate(column_name)]...
[by column_name [, column_name]...]
You cannot use text or image columns in a compute clause.
A system test may fail because there are too many aggregates in the compute clause of a query. The number of aggregates that each compute clause can accommodate is limited to 127, and if a compute clause contains more than 127 aggregates, the system generates an error message when you try to execute the query.
Each avg() aggregate counts as two aggregates when you are counting toward the limit of 127, because an avg() aggregate is actually a combination of a sum() aggregate and a count() aggregate.
Following are two queries and their results. The first one uses group by and aggregates. The second uses compute and row aggregates. Note the difference in the results.
select type, sum(price), sum(advance)
from titles
group by type
type
------------ ------- ----------
UNDECIDED NULL NULL
business 54.92 25,125.00
mod_cook 22.98 15,000.00
popular_comp 42.95 15,000.00
psychology 67.52 21,275.00
trad_cook 47.89 19,000.00
(6 rows affected)
select type, price, advance
from titles
order by type
compute sum(price), sum(advance) by type
type price advance
------------ ------------------------ --------
UNDECIDED NULL NULL
Compute Result:
------------------------ ------------------------
NULL NULL
type price advance
------------ -------------------- ----------
business 2.99 10,125.00
business 11.95 5,000.00
business 19.99 5,000.00
business 19.99 5,000.00
Compute Result:
------------------------ ------------------------
54.92 25,125.00
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
------------- ------------------- ------------
popular_comp NULL NULL
popular_comp 20.00 8,000.00
popular_comp 22.95 7,000.00
Compute Result:
------------------------ ------------------------
42.95 15,000.00
type price advance
------------ ------------------------ --------
psychology 7.00 6,000.00
psychology 7.99 4,000.00
psychology 10.95 2,275.00
psychology 19.99 2,000.00
psychology 21.59 7,000.00
Compute Result:
------------------------ ------------------------
67.52 21,275.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:Each summary value is treated as a row.
------------------------ ------------------------
47.89 19,000.00
(24 rows affected)
Row aggregates and compute
The row aggregates used with compute are listed in Table 3-2:| Row aggregates | Result |
| sum | Total of the values in the expression |
| avg | Average of the values in the expression |
| max | Highest value in the expression |
| min | Lowest value in the expression |
| count | Number of selected rows |
Rules for compute clauses
- Adaptive Server does not allow the distinct keyword with the row aggregates.
- The columns in a compute clause must appear in the select list.
- You cannot use select into (see Chapter 7, "Creating Databases and Tables") in the same statement as a compute clause because statements that include compute do not generate normal rows.
- If you use compute with the by keyword, you must also use an order by clause. The columns listed after 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, suppose the order by clause is:order by a, b, c
The compute clause can be any or all of these:compute row_aggregate (column_name) by a, b, c
compute row_aggregate (column_name) by a, b
compute row_aggregate (column_name) by a
The compute clause cannot be any of these:compute row_aggregate (column_name) by b, c
compute row_aggregate (column_name) by a, c
compute row_aggregate (column_name) by c
You must use a column name or an expression in the order by clause; you cannot sort by a column heading. - 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. The compute keyword without by is discussed under "Grand values: compute without by".
Specifying more than one column after compute
Listing more than one column after the by keyword affects the query by breaking a group into subgroups and applying the specified row aggregate to each level of grouping. For example, this query finds the sum of the prices of psychology books from each publisher:select type, pub_id, price
from titles
where type = "psychology"
order by type, pub_id, price
compute sum(price) by type, pub_id
type pub_id price
----------- ------- -------------
psychology 0736 7.00
psychology 0736 7.99
psychology 0736 10.95
psychology 0736 19.99
Compute Result:
---------------
45.93
type pub_id price
----------- ------- -------------
psychology 0877 21.59
Compute Result:
---------------
21.59
(7 rows affected)
Using more than one compute clause
You can use different aggregates in the same statement by including more than one compute clause. The following query is similar to the preceding one but adds the sum of the prices of psychology books by publisher:select type, pub_id, price
from titles
where 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 7.00
psychology 0736 7.99
psychology 0736 10.95
psychology 0736 19.99
Compute Result:
---------------
45.93
type pub_id price
---------- ------- --------------
psychology 0877 21.59
Compute Result:
---------------
21.59
Compute Result:
---------------
67.52
(8 rows affected)
Applying an aggregate to more than one column
One compute clause can apply the same aggregate to several columns. This query finds the sum of the prices and advances for each type of cookbook:select type, price, advance
from titles
where type like "%cook"
order by type
compute sum(price), sum(advance) by type
type price advanceRemember, the columns to which the aggregates apply must also be in the select list.
--------- ---------------- ---------------
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)
Using different aggregates in the same compute clause
You can use different aggregates in the same compute clause:select type, pub_id, price
from titles
where type like "%cook"
order by type, pub_id
compute sum(price), max(pub_id) by type
type pub_id price
----------- ------- --------------
mod_cook 0877 2.99
mod_cook 0877 19.99
Compute Result:
--------------- ----
22.98 0877
type pub_id price
----------- ------- --------------
trad_cook 0877 11.95
trad_cook 0877 14.99
trad_cook 0877 20.95
Compute Result:
--------------- ----
47.89 0877
(7 rows affected)
Grand values: compute without by
You can use the compute keyword without by to generate grand totals, grand counts, and so on.This statement finds the grand total of the prices and advances of all types of books that cost more than $20:
select type, price, advance
from titles
where price > $20
compute sum(price), sum(advance)
type price advanceYou can use a compute with by and a compute without by in the same query. The following query finds the sum of prices and advances by type and then computes the grand total of prices and advances for all types of books.
------------ ---------------- -------------
popular_comp 22.95 7,000.00
psychology 21.59 7,000.00
trad_cook 20.95 7,000.00
Compute Result:
--------------- ---------
65.49 21,000.00
(4 rows affected)
select type, price, advance
from titles
where type like "%cook"
order by type
compute sum(price), sum(advance) by type
compute sum(price), sum(advance)
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
Compute Result:
--------------- ---------
70.87 34,000.00
(8 rows affected)
No comments:
Post a Comment