Large Database Features In Oracle, conclusion - cube
(Page 4 of 7 )

The cube extension takes rollup a step further by generating subtotals for each combination of the group by attributes, totals by attribute, and the grand total. The following is an example of the cube extension, using the same query we used for rollup:

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 cube(c.cust_gender,
               b.channel_class,
               to_char(a.time_id, 'yyyy-mm'));

GENDER	CHANNEL_CLASS	MONTH	UNIT_COUNT	AMOUNT_SOLD
----------	----------------------	---------	----------------	-------------------
			39924	4216833.67
		2001-01	20739	2113709.13
		2001-02	19185	2103124.54
	Direct		20427	2113199.92
	Direct	2001-01	11039	1106147.18
	Direct	2001-02	9388	1007052.74
	Others		12603	1278645.06
	Others	2001-01	6796	657218.93
	Others	2001-02	5807	621426.13
	Indirect		6894	824988.69
	Indirect	2001-01	2904	350343.02
	Indirect	2001-02	3990	474645.67
F			14274	1552931.54
F		2001-01	7540	768012.01
F		2001-02	6734	784919.53
F	Direct		7209	752861.03
F	Direct	2001-01	4001	387000.9
F	Direct	2001-02	3208	365860.13
F	Others		4542	472249.42
F	Others	2001-01	2486	242615.9
F	Others	2001-02	2056	229633.52
F	Indirect		2523	327821.09
F	Indirect	2001-01	1053	138395.21
F	Indirect	2001-02	1470	189425.88
M			25650	2663902.13
M		2001-01	13199	1345697.12
M		2001-02	12451	1318205.01
M	Direct		13218	1360338.89
M	Direct	2001-01	7038	719146.28
M	Direct	2001-02	6180	641192.61
M	Others		8061	806395.64
M	Others	2001-01	4310	414603.03
M	Others	2001-02	3751	391792.61
M	Indirect		4371	497167.6
M	Indirect	2001-01	1851	211947.81
M	Indirect	2001-02	2520	285219.79

In the results, we can see that counts and sums of amount_sold are returned at the following levels:

• By GENDER, CHANNEL_CLASS, and MONTH
  
• Subtotals by MONTH within CHANNEL_CLASS
  
• Subtotals by MONTH within GENDER
  
• Subtotals by CHANNEL_CLASS within GENDER
  
• Subtotals by MONTH
  
• Subtotals by CHANNEL_CLASS
  
• Subtotals by GENDER
  
• Grand total

   

Next: Analysis Functions >>
 

More Oracle Articles
More By McGraw-Hill/Osborne

Comments On: Large Database Features In Oracle, conclusion

· This article is an excerpt from the book "Oracle Database 10g A Beginner's Guide,"...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter nine of the book Oracle Database 10g A Beginner's Guide, written by Ian Abramson et al (Mcgraw-Hill/Osborne, 2004; ISBN: 0072230789). Check it out at your favorite bookstore. Buy this book now.