olap.result 4.59 KB
drop table if exists sales;
create table sales ( product varchar(32), country varchar(32), year int, profit int);
insert into sales  values ( 'Computer', 'India',2000, 1200),
( 'TV', 'United States', 1999, 150),
( 'Calculator', 'United States', 1999,50),
( 'Computer', 'United States', 1999,1500),
( 'Computer', 'United States', 2000,1500),
( 'TV', 'United States', 2000, 150),
( 'TV', 'India', 2000, 100),
( 'TV', 'India', 2000, 100),
( 'Calculator', 'United States', 2000,75),
( 'Calculator', 'India', 2000,75),
( 'TV', 'India', 1999, 100),
( 'Computer', 'India', 1999,1200),
( 'Computer', 'United States', 2000,1500),
( 'Calculator', 'United States', 2000,75);
select product, country , year, sum(profit) from sales group by product, country, year with cube;
product	country	year	sum(profit)
Calculator	India	2000	75
Calculator	United States	1999	50
Calculator	United States	2000	150
Computer	India	1999	1200
Computer	India	2000	1200
Computer	United States	1999	1500
Computer	United States	2000	3000
TV	India	1999	100
TV	India	2000	200
TV	United States	1999	150
TV	United States	2000	150
Calculator	India	NULL	75
Calculator	United States	NULL	200
Computer	India	NULL	2400
Computer	United States	NULL	4500
TV	India	NULL	300
TV	United States	NULL	300
Calculator	NULL	1999	50
Calculator	NULL	2000	225
Computer	NULL	1999	2700
Computer	NULL	2000	4200
TV	NULL	1999	250
TV	NULL	2000	350
NULL	India	1999	1300
NULL	India	2000	1475
NULL	United States	1999	1700
NULL	United States	2000	3300
Calculator	NULL	NULL	275
Computer	NULL	NULL	6900
TV	NULL	NULL	600
NULL	India	NULL	2775
NULL	United States	NULL	5000
NULL	NULL	1999	3000
NULL	NULL	2000	4775
NULL	NULL	NULL	7775
explain select product, country , year, sum(profit) from sales group by product, country, year with cube;
table	type	possible_keys	key	key_len	ref	rows	Extra
sales	ALL	NULL	NULL	NULL	NULL	14	Using temporary; Using filesort
sales	ALL	NULL	NULL	NULL	NULL	14	Using temporary; Using filesort
sales	ALL	NULL	NULL	NULL	NULL	14	Using temporary; Using filesort
sales	ALL	NULL	NULL	NULL	NULL	14	Using temporary; Using filesort
sales	ALL	NULL	NULL	NULL	NULL	14	Using temporary; Using filesort
sales	ALL	NULL	NULL	NULL	NULL	14	Using temporary; Using filesort
sales	ALL	NULL	NULL	NULL	NULL	14	Using temporary; Using filesort
sales	ALL	NULL	NULL	NULL	NULL	14	
select product, country , year, sum(profit) from sales group by product, country, year with rollup;
product	country	year	sum(profit)
Calculator	India	2000	75
Calculator	United States	1999	50
Calculator	United States	2000	150
Computer	India	1999	1200
Computer	India	2000	1200
Computer	United States	1999	1500
Computer	United States	2000	3000
TV	India	1999	100
TV	India	2000	200
TV	United States	1999	150
TV	United States	2000	150
NULL	India	1999	1300
NULL	India	2000	1475
NULL	United States	1999	1700
NULL	United States	2000	3300
NULL	NULL	1999	3000
NULL	NULL	2000	4775
NULL	NULL	NULL	7775
explain select product, country , year, sum(profit) from sales group by product, country, year with rollup;
table	type	possible_keys	key	key_len	ref	rows	Extra
sales	ALL	NULL	NULL	NULL	NULL	14	Using temporary; Using filesort
sales	ALL	NULL	NULL	NULL	NULL	14	Using temporary; Using filesort
sales	ALL	NULL	NULL	NULL	NULL	14	Using temporary; Using filesort
sales	ALL	NULL	NULL	NULL	NULL	14	
select product, country , year, sum(profit) from sales group by product, country, year with cube union all select product, country , year, sum(profit) from sales group by product, country, year with rollup;
product	country	year	sum(profit)
Calculator	India	2000	75
Calculator	United States	1999	50
Calculator	United States	2000	150
Computer	India	1999	1200
Computer	India	2000	1200
Computer	United States	1999	1500
Computer	United States	2000	3000
TV	India	1999	100
TV	India	2000	200
TV	United States	1999	150
TV	United States	2000	150
Calculator	India	NULL	75
Calculator	United States	NULL	200
Computer	India	NULL	2400
Computer	United States	NULL	4500
TV	India	NULL	300
TV	United States	NULL	300
Calculator	NULL	1999	50
Calculator	NULL	2000	225
Computer	NULL	1999	2700
Computer	NULL	2000	4200
TV	NULL	1999	250
TV	NULL	2000	350
NULL	India	1999	1300
NULL	India	2000	1475
NULL	United States	1999	1700
NULL	United States	2000	3300
Calculator	NULL	NULL	275
Computer	NULL	NULL	6900
TV	NULL	NULL	600
NULL	India	NULL	2775
NULL	United States	NULL	5000
NULL	NULL	1999	3000
NULL	NULL	2000	4775
NULL	NULL	NULL	7775
Calculator	India	2000	75
Calculator	United States	1999	50
Calculator	United States	2000	150
Computer	India	1999	1200
Computer	India	2000	1200
Computer	United States	1999	1500
Computer	United States	2000	3000
TV	India	1999	100
TV	India	2000	200
TV	United States	1999	150
TV	United States	2000	150
drop table sales;