The COMPUTE keyword can be used without BY to generate grand totals, grand counts, and so on.
This statement finds the grand total of the prices and advances for all types of books over $20:
SELECT type, price, advance |
FROM titles |
WHERE price > $20 |
COMPUTE SUM(price), SUM(advance) |
You can use COMPUTE BY and 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:
SELECT type, price, advance |
|||
FROM titles |
|||
WHERE type LIKE '%cook' |
|||
ORDER BY type, price |
|||
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 |
|
sum |
sum |
||
========== |
========== |
||
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 |
|
sum |
sum |
||
========== |
========== |
||
47.89 |
19,000.00 |
||
sum |
sum |
||
========== |
========== |
||
70.87 |
34,000.00 |
||
(8 row(s) affected) |
|||