Reference Manual: Commands

Home

Reference Manual: Commands

Chapter 1 Commands

compute clause

Usage

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 
```

Restrictions

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.

compute results appear as a new row or rows

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.

Table 1-4: compute by clauses and detail rows

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

Case sensitivity

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


	Expand Search