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