github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_aggr_count.result (about) 1 SELECT COUNT(NULL); 2 COUNT(NULL) 3 0 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 count(a) from t1; 10 count(a) 11 4 12 select count(b) from t1; 13 count(b) 14 4 15 select count(c) from t1; 16 count(c) 17 4 18 select count(d) from t1; 19 count(d) 20 4 21 select count(e) from t1; 22 count(e) 23 4 24 select count(f) from t1; 25 count(f) 26 4 27 select count(g) from t1; 28 count(g) 29 4 30 select count(h) from t1; 31 count(h) 32 4 33 select count(i) from t1; 34 count(i) 35 4 36 select count(k) from t1; 37 count(k) 38 4 39 select count(l) from t1; 40 count(l) 41 4 42 select count(m) from t1; 43 count(m) 44 4 45 select count(n) from t1; 46 count(n) 47 4 48 drop table t1; 49 create table t1(a int); 50 select count(*) from t1; 51 count(*) 52 0 53 insert into t1 values(null),(null),(null),(null); 54 select count(*) from t1; 55 count(*) 56 4 57 drop table t1; 58 CREATE TABLE t1 ( 59 bug_id bigint(9) NOT NULL, 60 groupset bigint(20) DEFAULT 0 NOT NULL, 61 assigned_to bigint(9) DEFAULT 0 NOT NULL, 62 bug_file_loc varchar(255) 63 ); 64 CREATE TABLE t2 ( 65 value varchar(255), 66 program varchar(64), 67 initialowner varchar(255) NOT NULL, 68 initialqacontact varchar(255) NOT NULL, 69 description varchar(255) NOT NULL 70 ); 71 INSERT INTO t1 VALUES (1,0,0,'TestComponent'); 72 INSERT INTO t1 VALUES (2,0,0,'BBBBBBBBBBBBB'); 73 INSERT INTO t1 VALUES (3,0,0,'TestComponent2'); 74 INSERT INTO t1 VALUES (4,0,0,'TestComponent2'); 75 INSERT INTO t2 VALUES ('TestComponent','TestProduct','id0001','',''); 76 INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - conversion','AAAAA','id0001','',''); 77 INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - generic','AAAAA','id0001','',''); 78 INSERT INTO t2 VALUES ('TestComponent2','TestProduct','id0001','',''); 79 select any_value(value),any_value(description),COUNT(bug_id) from t2 left join t1 on t2.value=t1.bug_file_loc group by value having COUNT(bug_id) IN (0,2); 80 any_value(value) any_value(description) COUNT(bug_id) 81 BBBBBBBBBBBBB - conversion 0 82 BBBBBBBBBBBBB - generic 0 83 TestComponent2 2 84 SELECT COUNT(t1.bug_id) >= COUNT(t2.value) from t1 join t2 on t1.bug_file_loc = t2.value; 85 COUNT(t1.bug_id) >= COUNT(t2.value) 86 true 87 drop table t1; 88 drop table t2; 89 create table t1 (grp int, a bigint unsigned, c char(10) not null); 90 insert into t1 values (1,1,"a"); 91 insert into t1 values (2,2,"b"); 92 insert into t1 values (2,3,"c"); 93 insert into t1 values (3,4,"E"); 94 insert into t1 values (3,5,"C"); 95 insert into t1 values (3,6,"D"); 96 select sum(a) from t1 where a > 10; 97 sum(a) 98 null 99 select count(distinct a),count(distinct grp) from t1; 100 count(distinct a) count(distinct grp) 101 6 3 102 insert into t1 values (null,null,''); 103 select count(distinct a),count(distinct grp) from t1; 104 count(distinct a) count(distinct grp) 105 6 3 106 create table t2 (grp int, a bigint unsigned, c char(10)); 107 insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp; 108 drop table t1; 109 drop table t2; 110 create table t1 (a smallint(6) primary key, c char(10), b varchar(255)); 111 INSERT INTO t1 VALUES (1,'1','1'); 112 INSERT INTO t1 VALUES (2,'2','2'); 113 INSERT INTO t1 VALUES (4,'4','4'); 114 select count(*) from t1; 115 count(*) 116 3 117 select count(*) from t1 where a = 1; 118 count(*) 119 1 120 select count(*) from t1 where a = 100; 121 count(*) 122 0 123 select count(*) from t1 where a >= 10; 124 count(*) 125 0 126 select count(a) from t1 where a = 1; 127 count(a) 128 1 129 select count(a) from t1 where a = 100; 130 count(a) 131 0 132 select count(a) from t1 where a >= 10; 133 count(a) 134 0 135 select count(b) from t1 where b >= 2; 136 count(b) 137 2 138 select count(b) from t1 where b >= 10; 139 count(b) 140 2 141 select count(c) from t1 where c = 10; 142 count(c) 143 0 144 drop table t1; 145 CREATE TABLE t1 (d DATETIME, i INT); 146 INSERT INTO t1 SELECT "2021-10-23 10:01:01", 1; 147 SELECT COUNT(i), i, COUNT(i)*i FROM t1 GROUP BY i; 148 COUNT(i) i COUNT(i)*i 149 1 1 1 150 SELECT COUNT(i), (i+0), COUNT(i)*(i+0) FROM t1 GROUP BY i; 151 COUNT(i) (i+0) COUNT(i)*(i+0) 152 1 1 1 153 DROP TABLE t1; 154 CREATE TABLE t1 (a int, b int); 155 select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; 156 count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 157 0 null null null null null null null 158 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; 159 a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 160 insert into t1 values (1,null); 161 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; 162 a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 163 1 0 null null null null null null null 164 insert into t1 values (1,null); 165 insert into t1 values (2,null); 166 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; 167 a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 168 1 0 null null null null null null null 169 2 0 null null null null null null null 170 insert into t1 values (2,1); 171 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; 172 a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 173 1 0 null null null null null null null 174 2 1 1 1.0 0.0 1 1 1 1 175 insert into t1 values (3,1); 176 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; 177 a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 178 1 0 null null null null null null null 179 2 1 1 1.0 0.0 1 1 1 1 180 3 1 1 1.0 0.0 1 1 1 1 181 drop table t1; 182 CREATE TABLE t1 (a INT, b INT); 183 INSERT INTO t1 VALUES (1,1),(1,2),(2,3); 184 SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a; 185 correlated columns in aggregate function is not yet implemented 186 SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a; 187 (SELECT COUNT(DISTINCT 12)) 188 1 189 1 190 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); 191 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) 192 2.0 2 2 2 1 1 1 2 2 0.0 0.0 2 193 DROP TABLE t1; 194 CREATE TABLE t1 (col1 decimal(16,12)); 195 INSERT INTO t1 VALUES (-5.00000000001),(-5.00000000002),(-5.00000000003),(-5.00000000000),(-5.00000000001),(-5.00000000002); 196 insert into t1 select * from t1; 197 select col1,count(col1),sum(col1),avg(col1) from t1 group by col1; 198 col1 count(col1) sum(col1) avg(col1) 199 -5.000000000010 4 -20.000000000040 -5.0000000000100000 200 -5.000000000020 4 -20.000000000080 -5.0000000000200000 201 -5.000000000030 2 -10.000000000060 -5.0000000000300000 202 -5.000000000000 2 -10.000000000000 -5.0000000000000000 203 DROP TABLE t1; 204 CREATE TABLE t1 (a VARCHAR(400)); 205 INSERT INTO t1 (a) VALUES ("A"), ("a"), ("a "), ("a "),("B"), ("b"), ("b "), ("b "); 206 SELECT COUNT(DISTINCT a) FROM t1; 207 COUNT(DISTINCT a) 208 8 209 DROP TABLE t1; 210 create table bug22555 (i smallint, s1 smallint, s2 smallint, o1 double, o2 double, e1 decimal, e2 decimal); 211 insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76); 212 select i, count(*) from bug22555 group by i; 213 i count(*) 214 1 1 215 2 1 216 3 1 217 select std(s1/s2) from bug22555 where i=1; 218 std(s1/s2) 219 0.0 220 select std(s1/s2) from bug22555 where i=2; 221 std(s1/s2) 222 0.0 223 select std(s1/s2) from bug22555 where i=3; 224 std(s1/s2) 225 0.0 226 select std(s1/s2) from bug22555 where i=1 group by i; 227 std(s1/s2) 228 0.0 229 select std(s1/s2) from bug22555 where i=2 group by i; 230 std(s1/s2) 231 0.0 232 select std(s1/s2) from bug22555 where i=3 group by i; 233 std(s1/s2) 234 0.0 235 select std(s1/s2) from bug22555 group by i order by i; 236 std(s1/s2) 237 0.0 238 0.0 239 0.0 240 select i, count(*), std(o1/o2) from bug22555 group by i order by i; 241 i count(*) std(o1/o2) 242 1 1 0.0 243 2 1 0.0 244 3 1 0.0 245 select i, count(*), std(e1/e2) from bug22555 group by i order by i; 246 i count(*) std(e1/e2) 247 1 1 0.0 248 2 1 0.0 249 3 1 0.0 250 select i, count(*), variance(s1/s2) from bug22555 group by i order by i; 251 i count(*) variance(s1/s2) 252 1 1 0.0 253 2 1 0.0 254 3 1 0.0 255 select i, count(*), variance(o1/o2) from bug22555 group by i order by i; 256 i count(*) variance(o1/o2) 257 1 1 0.0 258 2 1 0.0 259 3 1 0.0 260 select i, count(*), variance(e1/e2) from bug22555 group by i order by i; 261 i count(*) variance(e1/e2) 262 1 1 0.0 263 2 1 0.0 264 3 1 0.0 265 select i, count(*), std(s1/s2) from bug22555 group by i order by i; 266 i count(*) std(s1/s2) 267 1 1 0.0 268 2 1 0.0 269 3 1 0.0 270 select i, count(*), std(o1/o2) from bug22555 group by i order by i; 271 i count(*) std(o1/o2) 272 1 1 0.0 273 2 1 0.0 274 3 1 0.0 275 select i, count(*), std(e1/e2) from bug22555 group by i order by i; 276 i count(*) std(e1/e2) 277 1 1 0.0 278 2 1 0.0 279 3 1 0.0 280 select i, count(*), variance(s1/s2) from bug22555 group by i order by i; 281 i count(*) variance(s1/s2) 282 1 1 0.0 283 2 1 0.0 284 3 1 0.0 285 select i, count(*), variance(o1/o2) from bug22555 group by i order by i; 286 i count(*) variance(o1/o2) 287 1 1 0.0 288 2 1 0.0 289 3 1 0.0 290 select i, count(*), variance(e1/e2) from bug22555 group by i order by i; 291 i count(*) variance(e1/e2) 292 1 1 0.0 293 2 1 0.0 294 3 1 0.0 295 select i, count(*), std(s1/s2) from bug22555 group by i order by i; 296 i count(*) std(s1/s2) 297 1 1 0.0 298 2 1 0.0 299 3 1 0.0 300 select i, count(*), std(o1/o2) from bug22555 group by i order by i; 301 i count(*) std(o1/o2) 302 1 1 0.0 303 2 1 0.0 304 3 1 0.0 305 select i, count(*), std(e1/e2) from bug22555 group by i order by i; 306 i count(*) std(e1/e2) 307 1 1 0.0 308 2 1 0.0 309 3 1 0.0 310 insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76); 311 insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76); 312 insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76); 313 select i, count(*), std(s1/s2) from bug22555 group by i order by i; 314 i count(*) std(s1/s2) 315 1 4 0.0 316 2 4 0.0 317 3 4 0.0 318 select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i; 319 i count(*) round(std(o1/o2), 16) 320 1 4 0.0 321 2 4 0.0 322 3 4 0.0 323 select i, count(*), std(e1/e2) from bug22555 group by i order by i; 324 i count(*) std(e1/e2) 325 1 4 0.0 326 2 4 0.0 327 3 4 0.0 328 select std(s1/s2) from bug22555; 329 std(s1/s2) 330 0.21325763593256278 331 select std(o1/o2) from bug22555; 332 std(o1/o2) 333 0.2132576358664934 334 select std(e1/e2) from bug22555; 335 std(e1/e2) 336 0.21325763593256278 337 select i, count(*), std(s1/s2) from bug22555 group by i order by i; 338 i count(*) std(s1/s2) 339 1 4 0.0 340 2 4 0.0 341 3 4 0.0 342 select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i; 343 i count(*) round(std(o1/o2), 16) 344 1 4 0.0 345 2 4 0.0 346 3 4 0.0 347 select i, count(*), std(e1/e2) from bug22555 group by i order by i; 348 i count(*) std(e1/e2) 349 1 4 0.0 350 2 4 0.0 351 3 4 0.0 352 select round(std(s1/s2), 17) from bug22555; 353 round(std(s1/s2), 17) 354 0.21325763593256275 355 select std(o1/o2) from bug22555; 356 std(o1/o2) 357 0.2132576358664934 358 select round(std(e1/e2), 17) from bug22555; 359 round(std(e1/e2), 17) 360 0.21325763593256275 361 drop table bug22555; 362 create table t1 (a decimal(20)); 363 insert into t1 values (12345678901234567890); 364 select count(a) from t1; 365 count(a) 366 1 367 select count(distinct a) from t1; 368 count(distinct a) 369 1 370 drop table t1; 371 CREATE TABLE t1 (a INT, b INT); 372 INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); 373 INSERT INTO t1 SELECT a, b+8 FROM t1; 374 INSERT INTO t1 SELECT a, b+16 FROM t1; 375 INSERT INTO t1 SELECT a, b+32 FROM t1; 376 INSERT INTO t1 SELECT a, b+64 FROM t1; 377 INSERT INTO t1 SELECT a, b+128 FROM t1; 378 INSERT INTO t1 SELECT a, b+256 FROM t1; 379 INSERT INTO t1 SELECT a, b+512 FROM t1; 380 INSERT INTO t1 SELECT a, b+1024 FROM t1; 381 INSERT INTO t1 SELECT a, b+2048 FROM t1; 382 INSERT INTO t1 SELECT a, b+4096 FROM t1; 383 INSERT INTO t1 SELECT a, b+8192 FROM t1; 384 INSERT INTO t1 SELECT a, b+16384 FROM t1; 385 INSERT INTO t1 SELECT a, b+32768 FROM t1; 386 SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50; 387 SQL syntax error: column "cnt" must appear in the GROUP BY clause or be used in an aggregate function 388 SELECT a, SUM( b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50; 389 SQL syntax error: column "sumation" must appear in the GROUP BY clause or be used in an aggregate function 390 SELECT a, AVG( b) AS average FROM t1 GROUP BY a HAVING average > 50; 391 SQL syntax error: column "average" must appear in the GROUP BY clause or be used in an aggregate function 392 SELECT a,COUNT(DISTINCT b) FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 50; 393 a COUNT(DISTINCT b) 394 1 65536 395 SELECT DISTINCT a, SUM( b) FROM t1 GROUP BY a HAVING SUM( b) > 50; 396 a SUM( b) 397 1 2147516416 398 SELECT DISTINCT a, AVG( b) FROM t1 GROUP BY a HAVING AVG( b) > 50; 399 a AVG( b) 400 1 32768.5000 401 DROP TABLE t1;