olap.result 4.47 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138
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	0	75
Calculator	United States	0	200
Computer	India	0	2400
Computer	United States	0	4500
TV	India	0	300
TV	United States	0	300
Calculator	ALL	1999	50
Calculator	ALL	2000	225
Computer	ALL	1999	2700
Computer	ALL	2000	4200
TV	ALL	1999	250
TV	ALL	2000	350
ALL	India	1999	1300
ALL	India	2000	1475
ALL	United States	1999	1700
ALL	United States	2000	3300
Calculator	ALL	0	275
Computer	ALL	0	6900
TV	ALL	0	600
ALL	India	0	2775
ALL	United States	0	5000
ALL	ALL	1999	3000
ALL	ALL	2000	4775
ALL	ALL	0	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
ALL	India	1999	1300
ALL	India	2000	1475
ALL	United States	1999	1700
ALL	United States	2000	3300
ALL	ALL	1999	3000
ALL	ALL	2000	4775
ALL	ALL	0	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	0	75
Calculator	United States	0	200
Computer	India	0	2400
Computer	United States	0	4500
TV	India	0	300
TV	United States	0	300
Calculator	ALL	1999	50
Calculator	ALL	2000	225
Computer	ALL	1999	2700
Computer	ALL	2000	4200
TV	ALL	1999	250
TV	ALL	2000	350
ALL	India	1999	1300
ALL	India	2000	1475
ALL	United States	1999	1700
ALL	United States	2000	3300
Calculator	ALL	0	275
Computer	ALL	0	6900
TV	ALL	0	600
ALL	India	0	2775
ALL	United States	0	5000
ALL	ALL	1999	3000
ALL	ALL	2000	4775
ALL	ALL	0	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;