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