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 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;