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