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