?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
core reorg
[csql.git] / test / sql / Aggregate / exp.test029.ksh
blob0062bac5c27c492504dfb9cb84f9bfd200f56ba8
1 Statement Executed
2 Statement Executed: Rows Affected = 1
3 Statement Executed: Rows Affected = 1
4 Statement Executed: Rows Affected = 1
5 Statement Executed: Rows Affected = 1
6 Statement Executed: Rows Affected = 1
7 Statement Executed: Rows Affected = 1
8 Statement Executed: Rows Affected = 1
9 Statement Executed: Rows Affected = 1
10 Statement Executed: Rows Affected = 1
11 Statement Executed: Rows Affected = 1
12 Statement Executed: Rows Affected = 1
13 Statement Executed: Rows Affected = 1
14 Statement Executed: Rows Affected = 1
15 Statement Executed: Rows Affected = 1
16 Statement Executed: Rows Affected = 1
17 Statement Executed: Rows Affected = 1
18 echo select * from t1;
19 ---------------------------------------------------------
20 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5 t1.f6 t1.f7 t1.f8 t1.f9
21 ---------------------------------------------------------
22 10 100 1000 Bijay 1000.000000 100000.000000 2009/3/1 11:59:59.0 2009/3/1 11:59:59.0
23 11 110 1100 Jitendr 2000.000000 200000.000000 2009/3/2 11:59:58.0 2009/3/2 11:59:59.0
24 12 120 1000 Nihar 3000.000000 300000.000000 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0
25 13 130 NULL Kishor 4000.000000 400000.000000 2009/3/4 11:59:59.0 2009/3/4 11:59:59.0
26 14 100 1000 Praba 5000.000000 500000.000000 2009/3/1 11:59:58.0 2009/3/1 11:59:59.0
27 15 110 1100 Sanjit 6000.000000 600000.000000 2009/3/2 11:59:57.0 2009/3/2 11:59:59.0
28 16 120 1000 Sanjay NULL 700000.000000 2009/3/3 11:59:59.0 2009/3/3 11:59:59.0
29 17 130 1100 Arindam 8000.000000 800000.000000 2009/3/4 11:59:58.0 2009/3/4 11:59:59.0
30 10 100 1000 Bijay 9000.000000 900000.000000 2009/3/1 11:59:57.0 2009/3/1 11:59:59.0
31 11 NULL 1100 Jitendr 1000.000000 100000.000000 2009/3/2 11:59:58.0 2009/3/2 11:59:59.0
32 12 120 1000 Nihar 2000.000000 200000.000000 2009/3/3 NULL 2009/3/3 11:59:59.0
33 13 130 1100 Kishor 3000.000000 300000.000000 2009/3/5 11:59:56.0 2009/3/5 11:59:59.0
34 14 100 1000 Praba 4000.000000 400000.000000 2009/3/1 11:59:55.0 2009/3/4 11:59:59.0
35 15 110 NULL Sanjit 5000.000000 500000.000000 2009/3/2 11:59:56.0 2009/3/1 11:59:59.0
36 16 120 1000 Sanjay 6000.000000 600000.000000 2009/3/3 11:59:55.0 2009/3/3 11:59:59.0
37 17 130 1100 Arindam 7000.000000 700000.000000 2009/3/1 11:59:59.0 2009/3/1 11:59:59.0
38
39 echo select f1,f2,f3,f4,count(f5),min(f5) ,count(f6),min(f6) from t1 group by f1,f2,f3,f4;
40 ---------------------------------------------------------
41 f1 f2 f3 f4 COUNT(f5) MIN(f5) COUNT(f6) MIN(f6)
42 ---------------------------------------------------------
43 10 100 1000 Bijay 2 1000.000000 2 100000.000000
44 11 110 1100 Jitendr 1 2000.000000 1 200000.000000
45 12 120 1000 Nihar 2 2000.000000 2 200000.000000
46 13 130 NULL Kishor 1 4000.000000 1 400000.000000
47 14 100 1000 Praba 2 4000.000000 2 400000.000000
48 15 110 1100 Sanjit 1 6000.000000 1 600000.000000
49 16 120 1000 Sanjay 1 6000.000000 2 600000.000000
50 17 130 1100 Arindam 2 7000.000000 2 700000.000000
51 11 NULL 1100 Jitendr 1 1000.000000 1 100000.000000
52 13 130 1100 Kishor 1 3000.000000 1 300000.000000
53 15 110 NULL Sanjit 1 5000.000000 1 500000.000000
54
55 echo select f1,f2,f3,f4,count(f5),min(f5) ,count(f6),min(f6) from t1 group by f2,f1,f4,f3;
56 ---------------------------------------------------------
57 f1 f2 f3 f4 COUNT(f5) MIN(f5) COUNT(f6) MIN(f6)
58 ---------------------------------------------------------
59 10 100 1000 Bijay 2 1000.000000 2 100000.000000
60 11 110 1100 Jitendr 1 2000.000000 1 200000.000000
61 12 120 1000 Nihar 2 2000.000000 2 200000.000000
62 13 130 NULL Kishor 1 4000.000000 1 400000.000000
63 14 100 1000 Praba 2 4000.000000 2 400000.000000
64 15 110 1100 Sanjit 1 6000.000000 1 600000.000000
65 16 120 1000 Sanjay 1 6000.000000 2 600000.000000
66 17 130 1100 Arindam 2 7000.000000 2 700000.000000
67 11 NULL 1100 Jitendr 1 1000.000000 1 100000.000000
68 13 130 1100 Kishor 1 3000.000000 1 300000.000000
69 15 110 NULL Sanjit 1 5000.000000 1 500000.000000
70
71 echo select f1,f2,f3,f4,count(f5),min(f5) ,count(f6),min(f6) from t1 group by f3,f1,f4,f2;
72 ---------------------------------------------------------
73 f1 f2 f3 f4 COUNT(f5) MIN(f5) COUNT(f6) MIN(f6)
74 ---------------------------------------------------------
75 10 100 1000 Bijay 2 1000.000000 2 100000.000000
76 11 110 1100 Jitendr 1 2000.000000 1 200000.000000
77 12 120 1000 Nihar 2 2000.000000 2 200000.000000
78 13 130 NULL Kishor 1 4000.000000 1 400000.000000
79 14 100 1000 Praba 2 4000.000000 2 400000.000000
80 15 110 1100 Sanjit 1 6000.000000 1 600000.000000
81 16 120 1000 Sanjay 1 6000.000000 2 600000.000000
82 17 130 1100 Arindam 2 7000.000000 2 700000.000000
83 11 NULL 1100 Jitendr 1 1000.000000 1 100000.000000
84 13 130 1100 Kishor 1 3000.000000 1 300000.000000
85 15 110 NULL Sanjit 1 5000.000000 1 500000.000000
86
87 echo select f1,f2,f3,f4,count(f5),min(f5) ,count(f6),min(f6) from t1 group by f4,f3,f2,f1;
88 ---------------------------------------------------------
89 f1 f2 f3 f4 COUNT(f5) MIN(f5) COUNT(f6) MIN(f6)
90 ---------------------------------------------------------
91 10 100 1000 Bijay 2 1000.000000 2 100000.000000
92 11 110 1100 Jitendr 1 2000.000000 1 200000.000000
93 12 120 1000 Nihar 2 2000.000000 2 200000.000000
94 13 130 NULL Kishor 1 4000.000000 1 400000.000000
95 14 100 1000 Praba 2 4000.000000 2 400000.000000
96 15 110 1100 Sanjit 1 6000.000000 1 600000.000000
97 16 120 1000 Sanjay 1 6000.000000 2 600000.000000
98 17 130 1100 Arindam 2 7000.000000 2 700000.000000
99 11 NULL 1100 Jitendr 1 1000.000000 1 100000.000000
100 13 130 1100 Kishor 1 3000.000000 1 300000.000000
101 15 110 NULL Sanjit 1 5000.000000 1 500000.000000
102
103 echo select f1,f2,f3,f4,count(f5),min(f5) ,count(f6),min(f6) from t1 group by f1,f2,f3,f4 having min(f5)>=5000;
104 ---------------------------------------------------------
105 f1 f2 f3 f4 COUNT(f5) MIN(f5) COUNT(f6) MIN(f6)
106 ---------------------------------------------------------
107 15 110 1100 Sanjit 1 6000.000000 1 600000.000000
108 16 120 1000 Sanjay 1 6000.000000 2 600000.000000
109 17 130 1100 Arindam 2 7000.000000 2 700000.000000
110 15 110 NULL Sanjit 1 5000.000000 1 500000.000000
111
112 echo select f1,f2,f3,f4,count(f5),min(f5) ,count(f6),min(f6) from t1 where f1 >= 12 group by f1,f2,f3,f4;
113 ---------------------------------------------------------
114 f1 f2 f3 f4 COUNT(f5) MIN(f5) COUNT(f6) MIN(f6)
115 ---------------------------------------------------------
116 12 120 1000 Nihar 2 2000.000000 2 200000.000000
117 13 130 NULL Kishor 1 4000.000000 1 400000.000000
118 14 100 1000 Praba 2 4000.000000 2 400000.000000
119 15 110 1100 Sanjit 1 6000.000000 1 600000.000000
120 16 120 1000 Sanjay 1 6000.000000 2 600000.000000
121 17 130 1100 Arindam 2 7000.000000 2 700000.000000
122 13 130 1100 Kishor 1 3000.000000 1 300000.000000
123 15 110 NULL Sanjit 1 5000.000000 1 500000.000000
124
125 echo select f1,f2,f3,f4,count(f5),min(f5) ,count(f6),min(f6) from t1 where f1 >= 12 group by f4,f3,f1,f2;
126 ---------------------------------------------------------
127 f1 f2 f3 f4 COUNT(f5) MIN(f5) COUNT(f6) MIN(f6)
128 ---------------------------------------------------------
129 12 120 1000 Nihar 2 2000.000000 2 200000.000000
130 13 130 NULL Kishor 1 4000.000000 1 400000.000000
131 14 100 1000 Praba 2 4000.000000 2 400000.000000
132 15 110 1100 Sanjit 1 6000.000000 1 600000.000000
133 16 120 1000 Sanjay 1 6000.000000 2 600000.000000
134 17 130 1100 Arindam 2 7000.000000 2 700000.000000
135 13 130 1100 Kishor 1 3000.000000 1 300000.000000
136 15 110 NULL Sanjit 1 5000.000000 1 500000.000000
137
138 echo select f1,f2,f3,f4,count(f5),min(f5) ,count(f6),min(f6) from t1 where f1 >= 12 group by f4,f1,f2,f3;
139 ---------------------------------------------------------
140 f1 f2 f3 f4 COUNT(f5) MIN(f5) COUNT(f6) MIN(f6)
141 ---------------------------------------------------------
142 12 120 1000 Nihar 2 2000.000000 2 200000.000000
143 13 130 NULL Kishor 1 4000.000000 1 400000.000000
144 14 100 1000 Praba 2 4000.000000 2 400000.000000
145 15 110 1100 Sanjit 1 6000.000000 1 600000.000000
146 16 120 1000 Sanjay 1 6000.000000 2 600000.000000
147 17 130 1100 Arindam 2 7000.000000 2 700000.000000
148 13 130 1100 Kishor 1 3000.000000 1 300000.000000
149 15 110 NULL Sanjit 1 5000.000000 1 500000.000000
150
151 echo select f1,f2,f3,f4,count(f5),min(f5) ,count(f6),min(f6) from t1 where f1 >= 12 group by f4,f3,f1,f2;
152 ---------------------------------------------------------
153 f1 f2 f3 f4 COUNT(f5) MIN(f5) COUNT(f6) MIN(f6)
154 ---------------------------------------------------------
155 12 120 1000 Nihar 2 2000.000000 2 200000.000000
156 13 130 NULL Kishor 1 4000.000000 1 400000.000000
157 14 100 1000 Praba 2 4000.000000 2 400000.000000
158 15 110 1100 Sanjit 1 6000.000000 1 600000.000000
159 16 120 1000 Sanjay 1 6000.000000 2 600000.000000
160 17 130 1100 Arindam 2 7000.000000 2 700000.000000
161 13 130 1100 Kishor 1 3000.000000 1 300000.000000
162 15 110 NULL Sanjit 1 5000.000000 1 500000.000000
163
164 echo select f1,f2,f3,f4,count(f5),min(f5) ,count(f6),min(f6) from t1 where f1 >= 12 and f1<15 group by f1,f2,f3,f4;
165 ---------------------------------------------------------
166 f1 f2 f3 f4 COUNT(f5) MIN(f5) COUNT(f6) MIN(f6)
167 ---------------------------------------------------------
168 12 120 1000 Nihar 2 2000.000000 2 200000.000000
169 13 130 NULL Kishor 1 4000.000000 1 400000.000000
170 14 100 1000 Praba 2 4000.000000 2 400000.000000
171 13 130 1100 Kishor 1 3000.000000 1 300000.000000
172
173 echo select f1,f2,f3,f4,count(f5),min(f5) ,count(f6),min(f6) from t1 where f1 >= 12 and f1<15 group by f2,f3,f4,f1;
174 ---------------------------------------------------------
175 f1 f2 f3 f4 COUNT(f5) MIN(f5) COUNT(f6) MIN(f6)
176 ---------------------------------------------------------
177 12 120 1000 Nihar 2 2000.000000 2 200000.000000
178 13 130 NULL Kishor 1 4000.000000 1 400000.000000
179 14 100 1000 Praba 2 4000.000000 2 400000.000000
180 13 130 1100 Kishor 1 3000.000000 1 300000.000000
181
182 echo select f1,f2,f3,f4,count(f5),min(f5) ,count(f6),min(f6) from t1 where f1 >= 12 and f1<15 group by f1,f2,f4,f3;
183 ---------------------------------------------------------
184 f1 f2 f3 f4 COUNT(f5) MIN(f5) COUNT(f6) MIN(f6)
185 ---------------------------------------------------------
186 12 120 1000 Nihar 2 2000.000000 2 200000.000000
187 13 130 NULL Kishor 1 4000.000000 1 400000.000000
188 14 100 1000 Praba 2 4000.000000 2 400000.000000
189 13 130 1100 Kishor 1 3000.000000 1 300000.000000
190
191 echo select f1,f2,f3,f4,count(f5),min(f5) ,count(f6),min(f6) from t1 where f1 >= 12 and f1<15 group by f1,f2,f3,f4 having min(f5)>=4000 and min(f5)<6000;
192 ---------------------------------------------------------
193 f1 f2 f3 f4 COUNT(f5) MIN(f5) COUNT(f6) MIN(f6)
194 ---------------------------------------------------------
195 13 130 NULL Kishor 1 4000.000000 1 400000.000000
196 14 100 1000 Praba 2 4000.000000 2 400000.000000
197
198 echo select f1,f2,f3,f4,count(f5),min(f5) ,count(f6),min(f6) from t1 where f1 >= 12 and f2<=130 group by f1,f2,f3,f4;
199 ---------------------------------------------------------
200 f1 f2 f3 f4 COUNT(f5) MIN(f5) COUNT(f6) MIN(f6)
201 ---------------------------------------------------------
202 12 120 1000 Nihar 2 2000.000000 2 200000.000000
203 13 130 NULL Kishor 1 4000.000000 1 400000.000000
204 14 100 1000 Praba 2 4000.000000 2 400000.000000
205 15 110 1100 Sanjit 1 6000.000000 1 600000.000000
206 16 120 1000 Sanjay 1 6000.000000 2 600000.000000
207 17 130 1100 Arindam 2 7000.000000 2 700000.000000
208 13 130 1100 Kishor 1 3000.000000 1 300000.000000
209 15 110 NULL Sanjit 1 5000.000000 1 500000.000000
210
211 echo select f1,f2,f3,f4,count(f5),min(f5) ,count(f6),min(f6) from t1 where f1 >= 12 and f2<=130 group by f1,f2,f3,f4 having min(f5)>=5000 and min(f5)<10000;
212 ---------------------------------------------------------
213 f1 f2 f3 f4 COUNT(f5) MIN(f5) COUNT(f6) MIN(f6)
214 ---------------------------------------------------------
215 15 110 1100 Sanjit 1 6000.000000 1 600000.000000
216 16 120 1000 Sanjay 1 6000.000000 2 600000.000000
217 17 130 1100 Arindam 2 7000.000000 2 700000.000000
218 15 110 NULL Sanjit 1 5000.000000 1 500000.000000
219
220 Statement Executed
Main memory database SQL engine (cvs import)