?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
core reorg
[csql.git] / test / sql / Aggregate / exp.test035.ksh
blobaf162acc6081c0be619dd9822b45339b0444db3f
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,count(f4),count(f7),count(f8),count(f9) from t1 group by f1,f2;
40 ---------------------------------------------------------
41 f1 f2 COUNT(f4) COUNT(f7) COUNT(f8) COUNT(f9)
42 ---------------------------------------------------------
43 10 100 2 2 2 2
44 11 110 1 1 1 1
45 12 120 2 2 1 2
46 13 130 2 2 2 2
47 14 100 2 2 2 2
48 15 110 2 2 2 2
49 16 120 2 2 2 2
50 17 130 2 2 2 2
51 11 NULL 1 1 1 1
52
53 echo select f1,f2,count(f4),count(f7),count(f8),count(f9) from t1 group by f1,f2 having count(f4)>=2;
54 ---------------------------------------------------------
55 f1 f2 COUNT(f4) COUNT(f7) COUNT(f8) COUNT(f9)
56 ---------------------------------------------------------
57 10 100 2 2 2 2
58 12 120 2 2 1 2
59 13 130 2 2 2 2
60 14 100 2 2 2 2
61 15 110 2 2 2 2
62 16 120 2 2 2 2
63 17 130 2 2 2 2
64
65 echo select f1,f2,count(f4),count(f7),count(f8),count(f9) from t1 where f1 >= 12 group by f1,f2;
66 ---------------------------------------------------------
67 f1 f2 COUNT(f4) COUNT(f7) COUNT(f8) COUNT(f9)
68 ---------------------------------------------------------
69 12 120 2 2 1 2
70 13 130 2 2 2 2
71 14 100 2 2 2 2
72 15 110 2 2 2 2
73 16 120 2 2 2 2
74 17 130 2 2 2 2
75
76 echo select f1,f2,count(f4),count(f7),count(f8),count(f9) from t1 where f1 >= 12 group by f1,f2 having count(f8)>=1;
77 ---------------------------------------------------------
78 f1 f2 COUNT(f4) COUNT(f7) COUNT(f8) COUNT(f9)
79 ---------------------------------------------------------
80 12 120 2 2 1 2
81 13 130 2 2 2 2
82 14 100 2 2 2 2
83 15 110 2 2 2 2
84 16 120 2 2 2 2
85 17 130 2 2 2 2
86
87 echo select f1,f2,count(f4),count(f7),count(f8),count(f9) from t1 where f1 >= 12 and f1<15 group by f1,f2;
88 ---------------------------------------------------------
89 f1 f2 COUNT(f4) COUNT(f7) COUNT(f8) COUNT(f9)
90 ---------------------------------------------------------
91 12 120 2 2 1 2
92 13 130 2 2 2 2
93 14 100 2 2 2 2
94
95 echo select f1,f2,count(f4),count(f7),count(f8),count(f9) from t1 where f1 >= 12 and f1<15 group by f1,f2 having count(f9)>=2;
96 ---------------------------------------------------------
97 f1 f2 COUNT(f4) COUNT(f7) COUNT(f8) COUNT(f9)
98 ---------------------------------------------------------
99 12 120 2 2 1 2
100 13 130 2 2 2 2
101 14 100 2 2 2 2
102
103 echo select f1,f2,count(f4),count(f7),count(f8),count(f9) from t1 where f1 >= 12 and f1<15 group by f1,f2 having count(f4)>=2 and count(f8) <2;
104 ---------------------------------------------------------
105 f1 f2 COUNT(f4) COUNT(f7) COUNT(f8) COUNT(f9)
106 ---------------------------------------------------------
107 12 120 2 2 1 2
108
109 echo select f1,f2,count(f4),count(f7),count(f8),count(f9) from t1 where f1 >= 12 and f2<130 group by f1,f2 ;
110 ---------------------------------------------------------
111 f1 f2 COUNT(f4) COUNT(f7) COUNT(f8) COUNT(f9)
112 ---------------------------------------------------------
113 12 120 2 2 1 2
114 14 100 2 2 2 2
115 15 110 2 2 2 2
116 16 120 2 2 2 2
117
118 echo select f1,f2,count(f4),count(f7),count(f8),count(f9) from t1 where f1 >= 12 and f2<130 group by f1,f2 having count(f7)>=2 and count(f8)<>2;
119 ---------------------------------------------------------
120 f1 f2 COUNT(f4) COUNT(f7) COUNT(f8) COUNT(f9)
121 ---------------------------------------------------------
122 12 120 2 2 1 2
123
124 echo select f7,f8,f9,count(f4) from t1 group by f7,f8,f9;
125 ---------------------------------------------------------
126 f7 f8 f9 COUNT(f4)
127 ---------------------------------------------------------
128 2009/3/1 11:59:59.0 2009/3/1 11:59:59.0 2
129 2009/3/2 11:59:58.0 2009/3/2 11:59:59.0 2
130 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0 1
131 2009/3/4 11:59:59.0 2009/3/4 11:59:59.0 1
132 2009/3/1 11:59:58.0 2009/3/1 11:59:59.0 1
133 2009/3/2 11:59:57.0 2009/3/2 11:59:59.0 1
134 2009/3/3 11:59:59.0 2009/3/3 11:59:59.0 1
135 2009/3/4 11:59:58.0 2009/3/4 11:59:59.0 1
136 2009/3/1 11:59:57.0 2009/3/1 11:59:59.0 1
137 2009/3/3 NULL 2009/3/3 11:59:59.0 1
138 2009/3/5 11:59:56.0 2009/3/5 11:59:59.0 1
139 2009/3/1 11:59:55.0 2009/3/4 11:59:59.0 1
140 2009/3/2 11:59:56.0 2009/3/1 11:59:59.0 1
141 2009/3/3 11:59:55.0 2009/3/3 11:59:59.0 1
142
143 echo select f9,f7,f8,count(f4) from t1 group by f7,f8,f9;
144 ---------------------------------------------------------
145 f9 f7 f8 COUNT(f4)
146 ---------------------------------------------------------
147 2009/3/1 11:59:59.0 2009/3/1 11:59:59.0 2
148 2009/3/2 11:59:59.0 2009/3/2 11:59:58.0 2
149 2009/3/3 11:59:59.0 2009/3/3 11:59:57.0 1
150 2009/3/4 11:59:59.0 2009/3/4 11:59:59.0 1
151 2009/3/1 11:59:59.0 2009/3/1 11:59:58.0 1
152 2009/3/2 11:59:59.0 2009/3/2 11:59:57.0 1
153 2009/3/3 11:59:59.0 2009/3/3 11:59:59.0 1
154 2009/3/4 11:59:59.0 2009/3/4 11:59:58.0 1
155 2009/3/1 11:59:59.0 2009/3/1 11:59:57.0 1
156 2009/3/3 11:59:59.0 2009/3/3 NULL 1
157 2009/3/5 11:59:59.0 2009/3/5 11:59:56.0 1
158 2009/3/4 11:59:59.0 2009/3/1 11:59:55.0 1
159 2009/3/1 11:59:59.0 2009/3/2 11:59:56.0 1
160 2009/3/3 11:59:59.0 2009/3/3 11:59:55.0 1
161
162 echo select f7,f8,f9,count(f4) from t1 group by f7,f9,f8;
163 ---------------------------------------------------------
164 f7 f8 f9 COUNT(f4)
165 ---------------------------------------------------------
166 2009/3/1 11:59:59.0 2009/3/1 11:59:59.0 2
167 2009/3/2 11:59:58.0 2009/3/2 11:59:59.0 2
168 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0 1
169 2009/3/4 11:59:59.0 2009/3/4 11:59:59.0 1
170 2009/3/1 11:59:58.0 2009/3/1 11:59:59.0 1
171 2009/3/2 11:59:57.0 2009/3/2 11:59:59.0 1
172 2009/3/3 11:59:59.0 2009/3/3 11:59:59.0 1
173 2009/3/4 11:59:58.0 2009/3/4 11:59:59.0 1
174 2009/3/1 11:59:57.0 2009/3/1 11:59:59.0 1
175 2009/3/3 NULL 2009/3/3 11:59:59.0 1
176 2009/3/5 11:59:56.0 2009/3/5 11:59:59.0 1
177 2009/3/1 11:59:55.0 2009/3/4 11:59:59.0 1
178 2009/3/2 11:59:56.0 2009/3/1 11:59:59.0 1
179 2009/3/3 11:59:55.0 2009/3/3 11:59:59.0 1
180
181 echo select f7,f8,f9,count(f4) from t1 group by f7,f9,f8 having count(f4)>=1 and count(f4) <2;
182 ---------------------------------------------------------
183 f7 f8 f9 COUNT(f4)
184 ---------------------------------------------------------
185 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0 1
186 2009/3/4 11:59:59.0 2009/3/4 11:59:59.0 1
187 2009/3/1 11:59:58.0 2009/3/1 11:59:59.0 1
188 2009/3/2 11:59:57.0 2009/3/2 11:59:59.0 1
189 2009/3/3 11:59:59.0 2009/3/3 11:59:59.0 1
190 2009/3/4 11:59:58.0 2009/3/4 11:59:59.0 1
191 2009/3/1 11:59:57.0 2009/3/1 11:59:59.0 1
192 2009/3/3 NULL 2009/3/3 11:59:59.0 1
193 2009/3/5 11:59:56.0 2009/3/5 11:59:59.0 1
194 2009/3/1 11:59:55.0 2009/3/4 11:59:59.0 1
195 2009/3/2 11:59:56.0 2009/3/1 11:59:59.0 1
196 2009/3/3 11:59:55.0 2009/3/3 11:59:59.0 1
197
198 echo select f4,f8,f9,count(f7) from t1 group by f4,f8,f9;
199 ---------------------------------------------------------
200 f4 f8 f9 COUNT(f7)
201 ---------------------------------------------------------
202 Bijay 11:59:59.0 2009/3/1 11:59:59.0 1
203 Jitendr 11:59:58.0 2009/3/2 11:59:59.0 2
204 Nihar 11:59:57.0 2009/3/3 11:59:59.0 1
205 Kishor 11:59:59.0 2009/3/4 11:59:59.0 1
206 Praba 11:59:58.0 2009/3/1 11:59:59.0 1
207 Sanjit 11:59:57.0 2009/3/2 11:59:59.0 1
208 Sanjay 11:59:59.0 2009/3/3 11:59:59.0 1
209 Arindam 11:59:58.0 2009/3/4 11:59:59.0 1
210 Bijay 11:59:57.0 2009/3/1 11:59:59.0 1
211 Nihar NULL 2009/3/3 11:59:59.0 1
212 Kishor 11:59:56.0 2009/3/5 11:59:59.0 1
213 Praba 11:59:55.0 2009/3/4 11:59:59.0 1
214 Sanjit 11:59:56.0 2009/3/1 11:59:59.0 1
215 Sanjay 11:59:55.0 2009/3/3 11:59:59.0 1
216 Arindam 11:59:59.0 2009/3/1 11:59:59.0 1
217
218 echo select f4,f8,f9,count(f7) from t1 group by f4,f8,f9 having count(f7)<=2 and count(f7) >1 ;
219 ---------------------------------------------------------
220 f4 f8 f9 COUNT(f7)
221 ---------------------------------------------------------
222 Jitendr 11:59:58.0 2009/3/2 11:59:59.0 2
223
224 echo select f9,f7,f4,count(f8) from t1 group by f4,f7,f9;
225 ---------------------------------------------------------
226 f9 f7 f4 COUNT(f8)
227 ---------------------------------------------------------
228 2009/3/1 11:59:59.0 2009/3/1 Bijay 2
229 2009/3/2 11:59:59.0 2009/3/2 Jitendr 2
230 2009/3/3 11:59:59.0 2009/3/3 Nihar 1
231 2009/3/4 11:59:59.0 2009/3/4 Kishor 1
232 2009/3/1 11:59:59.0 2009/3/1 Praba 1
233 2009/3/2 11:59:59.0 2009/3/2 Sanjit 1
234 2009/3/3 11:59:59.0 2009/3/3 Sanjay 2
235 2009/3/4 11:59:59.0 2009/3/4 Arindam 1
236 2009/3/5 11:59:59.0 2009/3/5 Kishor 1
237 2009/3/4 11:59:59.0 2009/3/1 Praba 1
238 2009/3/1 11:59:59.0 2009/3/2 Sanjit 1
239 2009/3/1 11:59:59.0 2009/3/1 Arindam 1
240
241 echo select f9,f7,f4,count(f8) from t1 group by f4,f7,f9 having count(f8)<=2 and count(f8) >=2 ;
242 ---------------------------------------------------------
243 f9 f7 f4 COUNT(f8)
244 ---------------------------------------------------------
245 2009/3/1 11:59:59.0 2009/3/1 Bijay 2
246 2009/3/2 11:59:59.0 2009/3/2 Jitendr 2
247 2009/3/3 11:59:59.0 2009/3/3 Sanjay 2
248
249 echo select f7,f8,f4,count(f9) from t1 group by f4,f7,f8;
250 ---------------------------------------------------------
251 f7 f8 f4 COUNT(f9)
252 ---------------------------------------------------------
253 2009/3/1 11:59:59.0 Bijay 1
254 2009/3/2 11:59:58.0 Jitendr 2
255 2009/3/3 11:59:57.0 Nihar 1
256 2009/3/4 11:59:59.0 Kishor 1
257 2009/3/1 11:59:58.0 Praba 1
258 2009/3/2 11:59:57.0 Sanjit 1
259 2009/3/3 11:59:59.0 Sanjay 1
260 2009/3/4 11:59:58.0 Arindam 1
261 2009/3/1 11:59:57.0 Bijay 1
262 2009/3/3 NULL Nihar 1
263 2009/3/5 11:59:56.0 Kishor 1
264 2009/3/1 11:59:55.0 Praba 1
265 2009/3/2 11:59:56.0 Sanjit 1
266 2009/3/3 11:59:55.0 Sanjay 1
267 2009/3/1 11:59:59.0 Arindam 1
268
269 echo select f7,f8,f4,count(f9) from t1 group by f4,f7,f8 having count(f9)<=2 and count(f9) >=2 ;
270 ---------------------------------------------------------
271 f7 f8 f4 COUNT(f9)
272 ---------------------------------------------------------
273 2009/3/2 11:59:58.0 Jitendr 2
274
275 Statement Executed
Main memory database SQL engine (cvs import)