github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_aggr_avg.result (about) 1 SELECT avg(null); 2 avg(null) 3 null 4 create table t1(a tinyint, b SMALLINT, c bigint, d INT, e BIGINT, f FLOAT, g DOUBLE, h decimal(38,19), i DATE, k datetime, l TIMESTAMP, m char(255), n varchar(255)); 5 insert into t1 values(1, 1, 2, 4, 5, 5.5, 31.13, 14.314, "2012-03-12", "2012-03-12 10:03:12", "2012-03-12 13:03:12", "abc", "dcf"); 6 insert into t1 values(1, 1, 2, 4, 5, 5.5, 31.13, 14.314, "2012-03-12", "2012-03-12 10:03:12", "2012-03-12 13:03:12", "abc", "dcf"); 7 insert into t1 values(1, 1, 2, 4, 5, 5.5, 31.13, 14.314, "2012-03-12", "2012-03-12 10:03:12", "2012-03-12 13:03:12", "abc", "dcf"); 8 insert into t1 values(1, 1, 2, 4, 5, 5.5, 31.13, 14.314, "2012-03-12", "2012-03-12 10:03:12", "2012-03-12 13:03:12", "abc", "dcf"); 9 select avg(a) from t1; 10 avg(a) 11 1.0000 12 select avg(b) from t1; 13 avg(b) 14 1.0000 15 select avg(c) from t1; 16 avg(c) 17 2.0000 18 select avg(d) from t1; 19 avg(d) 20 4.0000 21 select avg(e) from t1; 22 avg(e) 23 5.0000 24 select avg(f) from t1; 25 avg(f) 26 5.5 27 select avg(g) from t1; 28 avg(g) 29 31.13 30 select avg(h) from t1; 31 avg(h) 32 14.31400000000000000000000 33 select avg(a) from t1 limit 1; 34 avg(a) 35 1.0000 36 select avg(b) from t1 limit 1; 37 avg(b) 38 1.0000 39 select avg(c) from t1 limit 1; 40 avg(c) 41 2.0000 42 select avg(d) from t1 limit 1; 43 avg(d) 44 4.0000 45 select avg(e) from t1 limit 1; 46 avg(e) 47 5.0000 48 select avg(f) from t1 limit 1; 49 avg(f) 50 5.5 51 select avg(g) from t1 limit 1; 52 avg(g) 53 31.13 54 select avg(h) from t1 limit 1; 55 avg(h) 56 14.31400000000000000000000 57 drop table t1; 58 create table t1(a bigint); 59 select avg(a) from t1; 60 avg(a) 61 null 62 insert into t1 values(null),(null),(null),(null); 63 select avg(a) from t1; 64 avg(a) 65 null 66 insert into t1 values(12417249128419),(124124125124151),(5124125151415),(124125152651515); 67 select avg(a) from t1 order by avg(a); 68 avg(a) 69 66447663013875.0000 70 drop table t1; 71 CREATE TABLE t1 ( 72 bug_id bigint(9) NOT NULL, 73 groupset bigint(20) DEFAULT 0 NOT NULL, 74 assigned_to bigint(9) DEFAULT 0 NOT NULL, 75 bug_file_loc varchar(255) 76 ); 77 CREATE TABLE t2 ( 78 value varchar(255), 79 program varchar(64), 80 initialowner varchar(255) NOT NULL, 81 initialqacontact varchar(255) NOT NULL, 82 description varchar(255) NOT NULL, 83 id int not null 84 ); 85 INSERT INTO t1 VALUES (1,0,0,'TestComponent'); 86 INSERT INTO t1 VALUES (2,0,0,'BBBBBBBBBBBBB'); 87 INSERT INTO t1 VALUES (3,0,0,'TestComponent2'); 88 INSERT INTO t1 VALUES (4,0,0,'TestComponent2'); 89 INSERT INTO t2 VALUES ('TestComponent','TestProduct','id0001','','',2); 90 INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - conversion','AAAAA','id0001','','',2); 91 INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - generic','AAAAA','id0001','','',3); 92 INSERT INTO t2 VALUES ('TestComponent2','TestProduct','id0001','','',3); 93 select any_value(value),any_value(description),avg(bug_id) from t2 left join t1 on t2.value=t1.bug_file_loc group by value having avg(bug_id)>0; 94 any_value(value) any_value(description) avg(bug_id) 95 TestComponent 1.0000 96 TestComponent2 3.5000 97 SELECT avg(t1.bug_id) >= avg(t2.id) from t1 join t2 on t1.bug_file_loc = t2.value; 98 avg(t1.bug_id) >= avg(t2.id) 99 true 100 drop table t1; 101 drop table t2; 102 create table t1 (a int); 103 insert t1 values (1),(2); 104 select if(1>2,any_value(a),avg(a)) from t1; 105 if(1>2,any_value(a),avg(a)) 106 1.5000 107 drop table t1; 108 CREATE TABLE t1(c0 INTEGER, c1 INTEGER, c2 INTEGER); 109 INSERT INTO t1 VALUES(1, 1, 1), (1, 1, 1); 110 SELECT CASE AVG (c0) WHEN any_value(c1) * any_value(c2) THEN 1 END FROM t1; 111 CASE AVG (c0) WHEN any_value(c1) * any_value(c2) THEN 1 END 112 1 113 SELECT CASE any_value(c1) * any_value(c2) WHEN SUM(c0) THEN 1 WHEN AVG(c0) THEN 2 END FROM t1; 114 CASE any_value(c1) * any_value(c2) WHEN SUM(c0) THEN 1 WHEN AVG(c0) THEN 2 END 115 2 116 SELECT CASE any_value(c1) WHEN any_value(c1) + 1 THEN 1 END, ABS(AVG(c0)) FROM t1; 117 CASE any_value(c1) WHEN any_value(c1) + 1 THEN 1 END ABS(AVG(c0)) 118 null 1.0000 119 DROP TABLE t1; 120 CREATE TABLE t1(a varchar(255), b INT, c INT UNSIGNED, d DECIMAL(12,2), e REAL); 121 INSERT INTO t1 VALUES('iynfj', 1, 1, 1, 1); 122 INSERT INTO t1 VALUES('innfj', 2, 2, 2, 2); 123 SELECT DISTINCT SUM( a ) FROM t1 GROUP BY a HAVING a IN ( AVG( 1 ), 1 + a); 124 invalid argument aggregate function sum, bad value [VARCHAR] 125 SELECT DISTINCT SUM( b ) FROM t1 GROUP BY b HAVING b IN ( AVG( 1 ), 1 + b); 126 SUM( b ) 127 1 128 SELECT DISTINCT SUM( c ) FROM t1 GROUP BY c HAVING c IN ( AVG( 1 ), 1 + c); 129 SUM( c ) 130 1 131 SELECT DISTINCT SUM( d ) FROM t1 GROUP BY d HAVING d IN ( AVG( 1 ), 1 + d); 132 SUM( d ) 133 1.00 134 SELECT DISTINCT SUM( e ) FROM t1 GROUP BY e HAVING e IN ( AVG( 1 ), 1 + e); 135 SUM( e ) 136 1.0 137 DROP TABLE t1; 138 CREATE TABLE t1 ( 139 a INT, 140 b INT, 141 c INT 142 ); 143 INSERT INTO t1 VALUES 144 ( 1, 1, 1 ), 145 ( 1, 2, 2 ), 146 ( 1, 3, 3 ), 147 ( 1, 4, 6 ), 148 ( 1, 5, 5 ), 149 ( 1, 9, 13 ), 150 ( 2, 1, 6 ), 151 ( 2, 2, 7 ), 152 ( 2, 3, 8 ); 153 SELECT a, AVG(t1.b), 154 (SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c, 155 (SELECT t12.c FROM t1 t12 WHERE t12.a = t1.a AND t12.b = AVG(t1.b)) AS t12c 156 FROM t1 GROUP BY a; 157 SQL syntax error: aggregate function avg not allowed in WHERE clause 158 DROP TABLE t1; 159 CREATE TABLE t1 (id int(11),value1 float(10,2)); 160 INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00); 161 CREATE TABLE t2 (id int(11),name char(20)); 162 INSERT INTO t2 VALUES (1,'Set One'),(2,'Set Two'); 163 select id, avg(value1), std(value1), variance(value1) from t1 group by id; 164 id avg(value1) std(value1) variance(value1) 165 1 1.0 0.816496580927726 0.6666666666666666 166 2 11.0 0.816496580927726 0.6666666666666666 167 select any_value(name), avg(value1), std(value1), variance(value1) from t1, t2 where t1.id = t2.id group by t1.id; 168 any_value(name) avg(value1) std(value1) variance(value1) 169 Set One 1.0 0.816496580927726 0.6666666666666666 170 Set Two 11.0 0.816496580927726 0.6666666666666666 171 drop table t1; 172 drop table t2; 173 create table t1 (id int not null); 174 create table t2 (id int not null,rating int null); 175 insert into t1 values(1),(2),(3); 176 insert into t2 values(1, 3),(2, NULL),(2, NULL),(3, 2),(3, NULL); 177 select t1.id, avg(rating) from t1 left join t2 on ( t1.id = t2.id ) group by t1.id; 178 id avg(rating) 179 1 3.0000 180 2 null 181 3 2.0000 182 drop table t1; 183 drop table t2; 184 CREATE TABLE t1 (a int, b int); 185 select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; 186 count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 187 0 null null null null null null null 188 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 189 a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 190 insert into t1 values (1,null); 191 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 192 a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 193 1 0 null null null null null null null 194 insert into t1 values (1,null); 195 insert into t1 values (2,null); 196 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 197 a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 198 1 0 null null null null null null null 199 2 0 null null null null null null null 200 insert into t1 values (2,1); 201 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 202 a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 203 1 0 null null null null null null null 204 2 1 1 1.0 0.0 1 1 1 1 205 insert into t1 values (3,1); 206 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 207 a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 208 1 0 null null null null null null null 209 2 1 1 1.0 0.0 1 1 1 1 210 3 1 1 1.0 0.0 1 1 1 1 211 drop table t1; 212 create table t1 (a int); 213 select avg(2) from t1; 214 avg(2) 215 null 216 drop table t1; 217 CREATE TABLE t1 (a INT, b INT); 218 INSERT INTO t1 VALUES (1,1),(1,2),(2,3); 219 SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a; 220 correlated columns in aggregate function is not yet implemented 221 SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a; 222 (SELECT COUNT(DISTINCT 12)) 223 1 224 1 225 SELECT AVG(2), BIT_AND(2), BIT_OR(2), BIT_XOR(2), COUNT(*), COUNT(12), COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2); 226 avg(2) bit_and(2) bit_or(2) bit_xor(2) count(*) count(12) count(distinct 12) min(2) max(2) std(2) variance(2) sum(2) 227 2.0 2 2 2 1 1 1 2 2 0.0 0.0 2 228 DROP TABLE t1; 229 CREATE TABLE t1 (col1 decimal(16,12)); 230 INSERT INTO t1 VALUES (-5.00000000001),(-5.00000000002),(-5.00000000003),(-5.00000000000),(-5.00000000001),(-5.00000000002); 231 insert into t1 select * from t1; 232 select col1,count(col1),sum(col1),avg(col1) from t1 group by col1; 233 col1 count(col1) sum(col1) avg(col1) 234 -5.000000000010 4 -20.000000000040 -5.0000000000100000 235 -5.000000000020 4 -20.000000000080 -5.0000000000200000 236 -5.000000000030 2 -10.000000000060 -5.0000000000300000 237 -5.000000000000 2 -10.000000000000 -5.0000000000000000 238 DROP TABLE t1; 239 CREATE TABLE t1 (a INT, b INT); 240 INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); 241 INSERT INTO t1 SELECT a, b+8 FROM t1; 242 INSERT INTO t1 SELECT a, b+16 FROM t1; 243 INSERT INTO t1 SELECT a, b+32 FROM t1; 244 INSERT INTO t1 SELECT a, b+64 FROM t1; 245 INSERT INTO t1 SELECT a, b+128 FROM t1; 246 INSERT INTO t1 SELECT a, b+256 FROM t1; 247 INSERT INTO t1 SELECT a, b+512 FROM t1; 248 INSERT INTO t1 SELECT a, b+1024 FROM t1; 249 INSERT INTO t1 SELECT a, b+2048 FROM t1; 250 INSERT INTO t1 SELECT a, b+4096 FROM t1; 251 INSERT INTO t1 SELECT a, b+8192 FROM t1; 252 INSERT INTO t1 SELECT a, b+16384 FROM t1; 253 INSERT INTO t1 SELECT a, b+32768 FROM t1; 254 SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50; 255 a cnt 256 1 65536 257 SELECT DISTINCT a, SUM( b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50; 258 a sumation 259 1 2147516416 260 SELECT DISTINCT a, AVG( b) AS average FROM t1 GROUP BY a HAVING average > 50; 261 a average 262 1 32768.5 263 SELECT a,COUNT(DISTINCT b) FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 50; 264 a COUNT(DISTINCT b) 265 1 65536 266 SELECT DISTINCT a, SUM( b) FROM t1 GROUP BY a HAVING SUM( b) > 50; 267 a SUM( b) 268 1 2147516416 269 SELECT DISTINCT a, AVG( b) FROM t1 GROUP BY a HAVING AVG( b) > 50; 270 a AVG( b) 271 1 32768.5000 272 DROP TABLE t1; 273 CREATE TABLE t1(a DOUBLE); 274 INSERT INTO t1 VALUES (10), (20); 275 SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1; 276 AVG(a) CAST(AVG(a) AS DECIMAL) 277 15.0 15 278 DROP TABLE t1; 279 create table t1 (grp int, a bigint unsigned, c char(10) not null); 280 insert into t1 values (1,1,"a"); 281 insert into t1 values (2,2,"b"); 282 insert into t1 values (2,3,"c"); 283 insert into t1 values (3,4,"E"); 284 insert into t1 values (3,5,"C"); 285 insert into t1 values (3,6,"D"); 286 select sum(all a),count(all a),avg(all a),std(all a),variance(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1; 287 sum(all a) count(all a) avg(all a) std(all a) variance(all a) bit_or(all a) bit_and(all a) min(all a) max(all a) min(all c) max(all c) 288 21 6 3.5000 1.707825127659933 2.9166666666666665 7 0 1 6 a E 289 select grp, sum(a),count(a),avg(a),std(a),variance(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp; 290 grp sum(a) count(a) avg(a) std(a) variance(a) bit_or(a) bit_and(a) min(a) max(a) min(c) max(c) 291 1 1 1 1.0000 0.0 0.0 1 1 1 1 a a 292 2 5 2 2.5000 0.5 0.25 3 2 2 3 b c 293 3 15 3 5.0000 0.816496580927726 0.6666666666666666 7 4 4 6 C E 294 select grp, sum(a)+count(a)+avg(a)+std(a)+variance(a)+bit_or(a)+bit_and(a)+min(a)+max(a)+min(c)+max(c) as sum from t1 group by grp; 295 strconv.ParseFloat: parsing "a": invalid syntax 296 create table t2 (grp int, a bigint unsigned, c char(10)); 297 insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp; 298 select * from t2; 299 grp a c 300 1 2 a 301 2 5 c 302 3 9 E 303 drop table t1; 304 drop table t2; 305 CREATE TABLE t1(a DOUBLE); 306 INSERT INTO t1 VALUES (10), (20); 307 SELECT AVG(min(a)) FROM t1 group by a; 308 SQL syntax error: aggregate function min calls cannot be nested 309 DROP TABLE t1; 310 CREATE TABLE t1 (id int(11),value1 float(10,2)); 311 INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00); 312 CREATE TABLE t2 (id int(11),name char(20)); 313 INSERT INTO t2 VALUES (1,'Set One'),(2,'Set Two'); 314 select name, avg(value1), std(value1), variance(value1) from t1, t2 where t1.id = t2.id group by t1.id; 315 SQL syntax error: column "t2.name" must appear in the GROUP BY clause or be used in an aggregate function 316 drop table t1; 317 drop table t2; 318 create table t1 (grp int, a bigint unsigned, c char(10) not null); 319 insert into t1 values (1,1,"a"); 320 insert into t1 values (2,2,"b"); 321 insert into t1 values (2,3,"c"); 322 insert into t1 values (3,4,"E"); 323 insert into t1 values (3,5,"C"); 324 insert into t1 values (3,6,"D"); 325 select avg(distinct a),avg(distinct grp) from t1; 326 avg(distinct a) avg(distinct grp) 327 3.5 2.0 328 insert into t1 values (null,null,''); 329 select avg(distinct a),avg(distinct grp) from t1; 330 avg(distinct a) avg(distinct grp) 331 3.5 2.0 332 drop table t1; 333 create table d1 (i1 int); 334 insert into d1 select * from generate_series(0, 999) g; 335 select avg(distinct i1) from d1; 336 avg(distinct i1) 337 499.5 338 drop table d1;