The COMPUTE clause is used in SELECT statements with row aggregate functions ¾ SUM, AVG, MIN, MAX, and COUNT ¾ to generate summary values based on the values in groups of rows.
These summary values appear as new rows in the query results, allowing you to see both detail and summary rows in one set of results. (Compare this to results from aggregate functions and the GROUP BY clause, which appear as new columns.)
The results of a query that includes a COMPUTE clause are like a control-break report, which is a report whose summary values are controlled by the groupings, or breaks, that you specify. You can produce summary values for groups, and you can calculate more than one aggregate function for the same group.
Here's how to use the COMPUTE clause to calculate the sum for the prices of the different types of cookbooks:
SELECT type, price |
||
FROM titles |
||
WHERE type like '%cook' |
||
ORDER BY type, price |
||
COMPUTE SUM(price) BY type |
||
type |
price |
|
-------- |
------------- |
|
mod_cook |
2.99 |
|
mod_cook |
19.99 |
|
sum |
||
============= |
||
22.98 |
||
type |
price |
|
--------- |
------------- |
|
trad_cook |
11.95 |
|
trad_cook |
14.99 |
|
trad_cook |
20.95 |
|
sum |
||
============= |
||
47.89 |
||
(7 row(s) affected) |
||
Notice that the summary values appear as new rows in the results, labeled with the word "sum," and that all the detail rows are also included.
With GROUP BY, detail rows are not shown, and the summary values are displayed as new columns. The same summary information can be produced with this statement:
SELECT type, SUM(price) |
||
FROM titles |
||
WHERE type LIKE '%cook' |
||
GROUP BY type |
||
type |
||
--------- |
------ |
|
mod_cook |
22.98 |
|
trad_cook |
47.89 |
|
(2 row(s) affected) |
||