github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/distinct/distinct.result (about) 1 drop table if exists t1; 2 create table t1( 3 a int, 4 b varchar(10) 5 ); 6 insert into t1 values (111, 'a'),(110, 'a'),(100, 'a'),(000, 'b'),(001, 'b'),(011,'b'); 7 select distinct b from t1; 8 b 9 a 10 b 11 select distinct b, a from t1; 12 b a 13 a 111 14 a 110 15 a 100 16 b 0 17 b 1 18 b 11 19 select count(distinct a) from t1; 20 count(distinct a) 21 6 22 select sum(distinct a) from t1; 23 sum(distinct a) 24 333 25 select avg(distinct a) from t1; 26 avg(distinct a) 27 55.5 28 select min(distinct a) from t1; 29 min(distinct a) 30 0 31 select max(distinct a) from t1; 32 max(distinct a) 33 111 34 drop table t1; 35 drop table if exists t2; 36 create table t2(a int, b varchar(10)); 37 insert into t2 values (1, 'a'); 38 insert into t2 values (2, NULL); 39 insert into t2 values (NULL, 'b'); 40 insert into t2 values (NULL, ''); 41 insert into t2 values (3, ''); 42 insert into t2 values (NULL, NULL); 43 select distinct a from t2; 44 a 45 1 46 2 47 null 48 3 49 select distinct b from t2; 50 b 51 a 52 null 53 b 54 55 select distinct a, b from t2; 56 a b 57 1 a 58 2 null 59 null b 60 null 61 3 62 null null 63 drop table t2; 64 drop table if exists t3; 65 create table t3 (i int, j int); 66 insert into t3 values (1,1), (1,2), (2,3), (2,4); 67 select i, count(distinct j) from t3 group by i; 68 i count(distinct j) 69 1 2 70 2 2 71 select i+0.0 as i2, count(distinct j) from t3 group by i2; 72 Column 'i2' does not exist 73 select i+0.0 as i2, count(distinct j) from t3 group by i; 74 i2 count(distinct j) 75 1.0 2 76 2.0 2 77 drop table t3; 78 drop table if exists t4; 79 CREATE TABLE t4 (a INT, b INT); 80 INSERT INTO t4 VALUES (1,1),(1,2),(2,3); 81 SELECT (SELECT COUNT(DISTINCT t4.b)) FROM t4 GROUP BY t4.a; 82 correlated columns in aggregate function is not yet implemented 83 SELECT (SELECT COUNT(DISTINCT 12)) FROM t4 GROUP BY t4.a; 84 (select count(distinct 12)) 85 1 86 1 87 drop table t4; 88 drop table if exists t5; 89 create table t5 (ff double); 90 insert into t5 values (2.2); 91 select cast(sum(distinct ff) as decimal(5,2)) from t5; 92 cast(sum(distinct ff) as decimal(5, 2)) 93 2.20 94 select cast(sum(distinct ff) as signed) from t5; 95 cast(sum(distinct ff) as ff) 96 2 97 select cast(variance(ff) as decimal(10,3)) from t5; 98 cast(variance(ff) as decimal(10, 3)) 99 0.000 100 select cast(min(ff) as decimal(5,2)) from t5; 101 cast(min(ff) as decimal(5, 2)) 102 2.20 103 drop table t5; 104 drop table if exists t6; 105 create table t6 (df decimal(5,1)); 106 insert into t6 values(1.1); 107 insert into t6 values(2.2); 108 select cast(sum(distinct df) as signed) from t6; 109 cast(sum(distinct df) as df) 110 3 111 select cast(min(df) as signed) from t6; 112 cast(min(df) as df) 113 1 114 select 1e8 * sum(distinct df) from t6; 115 1e8 * sum(distinct df) 116 3.3E8 117 select 1e8 * min(df) from t6; 118 1e8 * min(df) 119 1.1000000000000001E8 120 drop table t6; 121 drop table if exists t7; 122 CREATE TABLE t7 (a VARCHAR(400)); 123 INSERT INTO t7 (a) VALUES ("A"), ("a"), ("a "), ("a "), 124 ("B"), ("b"), ("b "), ("b "); 125 select * from t7; 126 a 127 A 128 a 129 a 130 a 131 B 132 b 133 b 134 b 135 SELECT COUNT(DISTINCT a) FROM t7; 136 count(distinct a) 137 8 138 DROP TABLE t7; 139 drop table if exists t8; 140 CREATE TABLE t8 (a INT, b INT); 141 INSERT INTO t8 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); 142 INSERT INTO t8 SELECT a, b+8 FROM t8; 143 INSERT INTO t8 SELECT a, b+16 FROM t8; 144 INSERT INTO t8 SELECT a, b+32 FROM t8; 145 INSERT INTO t8 SELECT a, b+64 FROM t8; 146 INSERT INTO t8 SELECT a, b+128 FROM t8; 147 INSERT INTO t8 SELECT a, b+256 FROM t8; 148 INSERT INTO t8 SELECT a, b+512 FROM t8; 149 INSERT INTO t8 SELECT a, b+1024 FROM t8; 150 INSERT INTO t8 SELECT a, b+2048 FROM t8; 151 INSERT INTO t8 SELECT a, b+4096 FROM t8; 152 INSERT INTO t8 SELECT a, b+8192 FROM t8; 153 INSERT INTO t8 SELECT a, b+16384 FROM t8; 154 INSERT INTO t8 SELECT a, b+32768 FROM t8; 155 SELECT a,COUNT(DISTINCT b) AS cnt FROM t8 GROUP BY a HAVING cnt > 50; 156 a cnt 157 1 65536 158 SELECT a,SUM(DISTINCT b) AS sumation FROM t8 GROUP BY a HAVING sumation > 50; 159 a sumation 160 1 2147516416 161 SELECT a,AVG(DISTINCT b) AS average FROM t8 GROUP BY a HAVING average > 50; 162 a average 163 1 32768.5 164 DROP TABLE t8; 165 drop table if exists t9; 166 CREATE TABLE t9 (a INT); 167 INSERT INTO t9 values (),(),(); 168 select distinct * from t9; 169 a 170 null 171 drop table t9; 172 drop table if exists t10; 173 CREATE TABLE t10 (col_int_nokey int(11)); 174 INSERT INTO t10 VALUES (7),(8),(NULL); 175 SELECT AVG(DISTINCT col_int_nokey) FROM t10; 176 avg(distinct col_int_nokey) 177 7.5 178 SELECT AVG(DISTINCT outr.col_int_nokey) FROM t10 AS outr LEFT JOIN t10 AS outr2 ON 179 outr.col_int_nokey = outr2.col_int_nokey; 180 avg(distinct outr.col_int_nokey) 181 7.5 182 DROP TABLE t10; 183 drop table if exists t11; 184 CREATE TABLE t11(c1 CHAR(30)); 185 INSERT INTO t11 VALUES('111'),('222'); 186 SELECT DISTINCT substr(c1, 1, 2147483647) FROM t11; 187 substr(c1, 1, 2147483647) 188 111 189 222 190 SELECT DISTINCT substr(c1, 1, 2147483648) FROM t11; 191 substr(c1, 1, 2147483648) 192 111 193 222 194 SELECT DISTINCT substr(c1, -1, 2147483648) FROM t11; 195 substr(c1, -1, 2147483648) 196 1 197 2 198 SELECT DISTINCT substr(c1, -2147483647, 2147483648) FROM t11; 199 substr(c1, -2147483647, 2147483648) 200 201 SELECT DISTINCT substr(c1, 9223372036854775807, 23) FROM t11; 202 substr(c1, 9223372036854775807, 23) 203 204 DROP TABLE t11; 205 drop table if exists t12; 206 drop view if exists v1; 207 create table t12(pk int primary key); 208 create view v1 as select pk from t12 where pk < 20; 209 insert into t12 values (1), (2), (3), (4); 210 select distinct pk from v1; 211 pk 212 1 213 2 214 3 215 4 216 insert into t12 values (5), (6), (7); 217 select distinct pk from v1; 218 pk 219 1 220 2 221 3 222 4 223 5 224 6 225 7 226 drop view v1; 227 drop table t12; 228 SELECT AVG(2), BIT_AND(2), BIT_OR(2), BIT_XOR(2); 229 avg(2) bit_and(2) bit_or(2) bit_xor(2) 230 2.0 2 2 2 231 select count(*); 232 count(*) 233 1 234 select COUNT(12), COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2); 235 count(12) count(distinct 12) min(2) max(2) std(2) variance(2) sum(2) 236 1 1 2 2 0.0 0.0 2 237 drop table if exists t13; 238 CREATE TABLE t13(product VARCHAR(32),country_id INTEGER NOT NULL,year INTEGER,profit INTEGER); 239 INSERT INTO t13 VALUES ( 'Computer', 2,2000, 1200), 240 ( 'TV', 1, 1999, 150), 241 ( 'Calculator', 1, 1999,50), 242 ( 'Computer', 1, 1999,1500), 243 ( 'Computer', 1, 2000,1500), 244 ( 'TV', 1, 2000, 150), 245 ( 'TV', 2, 2000, 100), 246 ( 'TV', 2, 2000, 100), 247 ( 'Calculator', 1, 2000,75), 248 ( 'Calculator', 2, 2000,75), 249 ( 'TV', 1, 1999, 100), 250 ( 'Computer', 1, 1999,1200), 251 ( 'Computer', 2, 2000,1500), 252 ( 'Calculator', 2, 2000,75), 253 ( 'Phone', 3, 2003,10); 254 SELECT product, country_id, COUNT(*), COUNT(distinct year) FROM t13 GROUP BY product, country_id order by product; 255 product country_id count(*) count(distinct year) 256 Calculator 1 2 2 257 Calculator 2 2 1 258 Computer 2 2 1 259 Computer 1 3 2 260 Phone 3 1 1 261 TV 1 3 2 262 TV 2 2 1 263 drop table t13;