| Team Fly | |
Oracle Database 10g provides extensions to the standard SQL group by clause of the select statement that generate other totals as part of the result set that previously required multiple queries, nested subqueries, or importing into spreadsheet type applications. These extensions are rollup and cube.
The rollup extension generates subtotals for attributes specified in the group by clause, plus another row representing the grand total. The following is an example of the rollup extension, using the SALES table we have seen throughout this chapter:
select c.cust_gender gender,
b.channel_class channel_class,
to_char(a.time_id, 'yyyy-mm') month,
count(*) unit_count,
sum(a.amount_sold) amount_sold
from sales a, channels b, customers c
where a.channel_id = b.channel_id
and a.cust_id = c.cust_id
and to_char(a.time_id, 'yyyy-mm') between '2001-01' and '2001-02'
group by rollup(c.cust_gender,
b.channel_class,
to_char(a.time_id, 'yyyy-mm'));
GENDER CHANNEL_CLASS MONTH UNIT_COUNT AMOUNT_SOLD
------ -------------------- ------- ---------- -----------
F Direct 2001-01 4001 387000.9
F Direct 2001-02 3208 365860.13
F Direct 7209 752861.03
F Others 2001-01 2486 242615.9
F Others 2001-02 2056 229633.52
F Others 4542 472249.42
F Indirect 2001-01 1053 138395.21
F Indirect 2001-02 1470 189425.88
F Indirect 2523 327821.09
F 14274 1552931.54
M Direct 2001-01 7038 719146.28
M Direct 2001-02 6180 641192.61
M Direct 13218 1360338.89
M Others 2001-01 4310 414603.03
M Others 2001-02 3751 391792.61
M Others 8061 806395.64
M Indirect 2001-01 1851 211947.81
M Indirect 2001-0 2520 285219.79
M Indirect 4371 497167.6
M 25650 2663902.13
39924 4216833.67
| Team Fly | |