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 advance ------------ ---------------- ------------- 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)
You can use a compute with by and a compute without by in the same query. The following query finds the sums 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 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)
