1> /* "Compute" example shown in class on 10/12/1999 */
2>
3> /* Exercise: Produce a report showing sum of total sales and */
4> /* average price of books for each publisher and for each */
5> /* type of book, with including subtotals and a grand total. */
6> /* */
7> /* In the query setup, note the following: */
8> /* */
9> /* - all column names used must be in the select list */
10> /* - you must "order by" the columns to group on */
11> /* - "compute" without "by" computes grand totals */
12> /* */
13> /* Note that the results do not constitute a relational table */
14> /* and therefore are not subject to further SQL queries. */
15>
16> use pubs2
1>
2> select pub_id, type, total_sales, price
3> from titles
4> order by pub_id, type
5> compute sum(total_sales), avg(price) by pub_id, type
6> compute sum(total_sales), avg(price) by pub_id
7> compute sum(total_sales), avg(price)
8> /* */
pub_id type total_sales price
------ ------------ ----------- ------------------------
0736 business 18722 2.99
Compute Result:
----------- ------------------------
18722 2.99
pub_id type total_sales price
------ ------------ ----------- ------------------------
0736 psychology 111 7.00
0736 psychology 2045 10.95
0736 psychology 3336 7.99
0736 psychology 4072 19.99
Compute Result:
----------- ------------------------
9564 11.48
Compute Result:
----------- ------------------------
28286 9.78
pub_id type total_sales price
------ ------------ ----------- ------------------------
0877 UNDECIDED NULL NULL
Compute Result:
----------- ------------------------
NULL NULL
pub_id type total_sales price
------ ------------ ----------- ------------------------
0877 mod_cook 2032 19.99
0877 mod_cook 22246 2.99
Compute Result:
----------- ------------------------
24278 11.49
pub_id type total_sales price
------ ------------ ----------- ------------------------
0877 psychology 375 21.59
Compute Result:
----------- ------------------------
375 21.59
pub_id type total_sales price
------ ------------ ----------- ------------------------
0877 trad_cook 375 20.95
0877 trad_cook 4095 14.99
0877 trad_cook 15096 11.95
Compute Result:
----------- ------------------------
19566 15.96
Compute Result:
----------- ------------------------
44219 15.41
pub_id type total_sales price
------ ------------ ----------- ------------------------
1389 business 3876 11.95
1389 business 4095 19.99
1389 business 4095 19.99
Compute Result:
----------- ------------------------
12066 17.31
pub_id type total_sales price
------ ------------ ----------- ------------------------
1389 popular_comp NULL NULL
1389 popular_comp 4095 20.00
1389 popular_comp 8780 22.95
Compute Result:
----------- ------------------------
12875 21.48
Compute Result:
----------- ------------------------
24941 18.98
Compute Result:
----------- ------------------------
97446 14.77
(30 rows affected)