code reorg
[csql.git] / test / sql / Aggregate / exp.test014.ksh
blob395f3efab59965a0ea13adf80fdb3e14edda5dee
1 echo create table t1(f1 char(10),f2 char(10),f3 int);
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 echo select * from t1;
16 ---------------------------------------------------------
17 t1.f1 t1.f2 t1.f3
18 ---------------------------------------------------------
19 s y 5000
20 p q 4000
21 z y 2000
22 p y 3000
23 c q 1000
24 z y 1500
25 s y 3000
26 c t 2500
27 s p 2300
28 c q 4000
29 c y 5000
30 c p 3500
32 echo select f2,avg(f3) from t1 group by f2;
33 ---------------------------------------------------------
34 f2 AVG(f3)
35 ---------------------------------------------------------
36 y 3250.000000
37 q 3000.000000
38 t 2500.000000
39 p 2900.000000
41 echo select f2,avg(f3) from t1 where f3>=3000 group by f2 ;
42 ---------------------------------------------------------
43 f2 AVG(f3)
44 ---------------------------------------------------------
45 y 4000.000000
46 q 4000.000000
47 p 3500.000000
49 echo select f2,avg(f3) from t1 where f3>=2000 group by f2 having avg(f3)>3000;
50 ---------------------------------------------------------
51 f2 AVG(f3)
52 ---------------------------------------------------------
53 y 3600.000000
54 q 4000.000000
56 echo select f2,avg(f3) from t1 where f3>=2000 group by f2 having avg(f3)>3000 limit 1;
57 ---------------------------------------------------------
58 f2 AVG(f3)
59 ---------------------------------------------------------
60 y 3600.000000
62 echo select f2,avg(f3) from t1 where f3>=2000 or f2 = 'y' group by f2 having avg(f3)>3000;
63 ---------------------------------------------------------
64 f2 AVG(f3)
65 ---------------------------------------------------------
66 y 3250.000000
67 q 4000.000000
69 echo select f1,f2,avg(f3) from t1 where f3>=2000 or f2 = 'y' group by f1,f2 having avg(f3)>3000 ;
70 ---------------------------------------------------------
71 f1 f2 AVG(f3)
72 ---------------------------------------------------------
73 s y 4000.000000
74 p q 4000.000000
75 c q 4000.000000
76 c y 5000.000000
77 c p 3500.000000
79 echo select f2,f1,avg(f3) from t1 where f3>=2000 or f2 = 'y' group by f1,f2 having avg(f3)>3000;
80 ---------------------------------------------------------
81 f2 f1 AVG(f3)
82 ---------------------------------------------------------
83 y s 4000.000000
84 q p 4000.000000
85 q c 4000.000000
86 y c 5000.000000
87 p c 3500.000000
89 echo select f2,f1,avg(f3) from t1 where f3>=2000 or f2 = 'y' group by f1,f2 having avg(f3)>2000 ;
90 ---------------------------------------------------------
91 f2 f1 AVG(f3)
92 ---------------------------------------------------------
93 y s 4000.000000
94 q p 4000.000000
95 y p 3000.000000
96 t c 2500.000000
97 p s 2300.000000
98 q c 4000.000000
99 y c 5000.000000
100 p c 3500.000000
102 echo select f2,f1,avg(f3) from t1 where f3>=2000 or f2 = 'y' group by f1,f2 having avg(f3)>2000 limit 3 offset 2;
103 ---------------------------------------------------------
104 f2 f1 AVG(f3)
105 ---------------------------------------------------------
106 y p 3000.000000
107 t c 2500.000000
108 p s 2300.000000
110 Statement Executed