github.com/matrixorigin/matrixone@v0.7.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 SQL syntax error: column "cnt" must appear in the GROUP BY clause or be used in an aggregate function 157 SELECT a,SUM(DISTINCT b) AS sumation FROM t8 GROUP BY a HAVING sumation > 50; 158 SQL syntax error: column "sumation" must appear in the GROUP BY clause or be used in an aggregate function 159 SELECT a,AVG(DISTINCT b) AS average FROM t8 GROUP BY a HAVING average > 50; 160 SQL syntax error: column "average" must appear in the GROUP BY clause or be used in an aggregate function 161 DROP TABLE t8; 162 drop table if exists t9; 163 CREATE TABLE t9 (a INT); 164 INSERT INTO t9 values (),(),(); 165 select distinct * from t9; 166 a 167 null 168 drop table t9; 169 drop table if exists t10; 170 CREATE TABLE t10 (col_int_nokey int(11)); 171 INSERT INTO t10 VALUES (7),(8),(NULL); 172 SELECT AVG(DISTINCT col_int_nokey) FROM t10; 173 avg(distinct col_int_nokey) 174 7.5 175 SELECT AVG(DISTINCT outr.col_int_nokey) FROM t10 AS outr LEFT JOIN t10 AS outr2 ON 176 outr.col_int_nokey = outr2.col_int_nokey; 177 avg(distinct outr.col_int_nokey) 178 7.5 179 DROP TABLE t10; 180 drop table if exists t11; 181 CREATE TABLE t11(c1 CHAR(30)); 182 INSERT INTO t11 VALUES('111'),('222'); 183 SELECT DISTINCT substr(c1, 1, 2147483647) FROM t11; 184 substr(c1, 1, 2147483647) 185 111 186 222 187 SELECT DISTINCT substr(c1, 1, 2147483648) FROM t11; 188 substr(c1, 1, 2147483648) 189 111 190 222 191 SELECT DISTINCT substr(c1, -1, 2147483648) FROM t11; 192 substr(c1, -1, 2147483648) 193 1 194 2 195 SELECT DISTINCT substr(c1, -2147483647, 2147483648) FROM t11; 196 substr(c1, -2147483647, 2147483648) 197 198 SELECT DISTINCT substr(c1, 9223372036854775807, 23) FROM t11; 199 substr(c1, 9223372036854775807, 23) 200 201 DROP TABLE t11; 202 drop table if exists t12; 203 drop view if exists v1; 204 create table t12(pk int primary key); 205 create view v1 as select pk from t12 where pk < 20; 206 insert into t12 values (1), (2), (3), (4); 207 select distinct pk from v1; 208 pk 209 1 210 2 211 3 212 4 213 insert into t12 values (5), (6), (7); 214 select distinct pk from v1; 215 pk 216 1 217 2 218 3 219 4 220 5 221 6 222 7 223 drop view v1; 224 drop table t12; 225 SELECT AVG(2), BIT_AND(2), BIT_OR(2), BIT_XOR(2); 226 avg(2) bit_and(2) bit_or(2) bit_xor(2) 227 2.0 2 2 2 228 select count(*); 229 count(*) 230 1 231 select COUNT(12), COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2); 232 count(12) count(distinct 12) min(2) max(2) std(2) variance(2) sum(2) 233 1 1 2 2 0.0 0.0 2 234 drop table if exists t13; 235 CREATE TABLE t13(product VARCHAR(32),country_id INTEGER NOT NULL,year INTEGER,profit INTEGER); 236 INSERT INTO t13 VALUES ( 'Computer', 2,2000, 1200), 237 ( 'TV', 1, 1999, 150), 238 ( 'Calculator', 1, 1999,50), 239 ( 'Computer', 1, 1999,1500), 240 ( 'Computer', 1, 2000,1500), 241 ( 'TV', 1, 2000, 150), 242 ( 'TV', 2, 2000, 100), 243 ( 'TV', 2, 2000, 100), 244 ( 'Calculator', 1, 2000,75), 245 ( 'Calculator', 2, 2000,75), 246 ( 'TV', 1, 1999, 100), 247 ( 'Computer', 1, 1999,1200), 248 ( 'Computer', 2, 2000,1500), 249 ( 'Calculator', 2, 2000,75), 250 ( 'Phone', 3, 2003,10); 251 SELECT product, country_id, COUNT(*), COUNT(distinct year) FROM t13 GROUP BY product, country_id order by product; 252 product country_id count(*) count(distinct year) 253 Calculator 1 2 2 254 Calculator 2 2 1 255 Computer 2 2 1 256 Computer 1 3 2 257 Phone 3 1 1 258 TV 1 3 2 259 TV 2 2 1 260 drop table t13;