github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_aggr_sum.result (about) 1 drop table if exists t; 2 CREATE TABLE t (i1 INT, 3 d1 DOUBLE, 4 e2 DECIMAL(5,2)); 5 INSERT INTO t VALUES ( 6, 6.0, 10.0/3), 6 ( null, 9.0, 10.0/3), 7 ( 1, null, 10.0/3), 8 ( 2, 2.0, null ); 9 SELECT coalesce(e2,i1) nullif_c, 10 IF(e2 IS NULL,i1,e2) if_c, 11 SUM(d1) FROM t 12 GROUP BY e2,i1 ORDER BY nullif_c, SUM(d1); 13 nullif_c if_c SUM(d1) 14 2.00 2.00 2.0 15 3.33 3.33 null 16 3.33 3.33 6.0 17 3.33 3.33 9.0 18 DROP TABLE t; 19 CREATE TABLE t1(a INT); 20 CREATE TABLE t2(a INT); 21 INSERT INTO t1 VALUES (1),(2),(3),(4); 22 INSERT INTO t2 VALUES (1),(2); 23 SELECT t1.a, t2.a FROM t1 JOIN t2 24 ON t1.a+t2.a = (SELECT COUNT(*) FROM t1); 25 subquery in JOIN condition is not yet implemented 26 SELECT a + (SELECT SUM(a) + (SELECT COUNT(a) FROM t1) FROM t1) AS cnt 27 FROM t2; 28 cnt 29 15 30 16 31 drop table t1; 32 drop table t2; 33 CREATE TABLE t1 (name char(1) default NULL, val int default NULL); 34 INSERT INTO t1 VALUES ('a',1), ('a',2), ('a',2), ('a',2), ('a',3), ('a',6), ('a',7), ('a',11), ('a',11), ('a',12), ('a',13), ('a',13), ('a',20), ('b',2), ('b',3), ('b',4), ('b',5); 35 SELECT s.name, AVG(s.val) AS med FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name; 36 invalid argument aggregate function sum, bad value [BOOL] 37 drop table t1; 38 create table t1 (a integer, b integer); 39 insert into t1 values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1); 40 select sum(b) from t1 group by a; 41 sum(b) 42 4 43 4 44 4 45 select sum(b) from (select a,b from t1) y group by a; 46 sum(b) 47 4 48 4 49 4 50 drop table t1; 51 CREATE TABLE t1 52 (a INTEGER, 53 b INTEGER, 54 c INTEGER 55 ); 56 INSERT INTO t1 VALUES 57 (1, 1, 10), (1, 2, 20), (1, 3, 30), (2, 1, 40), (2, 2, 50), (2, 3, 60); 58 CREATE TABLE t2 59 (a INTEGER, 60 d INTEGER, 61 e INTEGER 62 ); 63 INSERT INTO t2 VALUES 64 (1, 6, 60), (2, 6, 60), (3, 6, 60); 65 WITH 66 cte AS 67 (SELECT SUM(c) AS c, SUM(b) AS b, a 68 FROM t1 69 GROUP BY a) 70 SELECT t2.a, (SELECT MIN(c) FROM cte AS cte2 WHERE t2.d = cte2.b) 71 FROM t2 LEFT JOIN cte AS cte1 ON t2.a = cte1.a 72 LEFT JOIN t2 AS tx ON tx.e = cte1.c; 73 a (SELECT MIN(c) FROM cte AS cte2 WHERE t2.d = cte2.b) 74 1 60 75 1 60 76 1 60 77 2 60 78 3 60 79 drop table t1; 80 drop table t2; 81 create table t1 (num double(12,2)); 82 insert into t1 values (144.54); 83 select sum(if(num is null,0.00,num)) from t1; 84 sum(if(num is null,0.00,num)) 85 144.54 86 drop table t1; 87 CREATE TABLE t1(c0 INTEGER, c1 INTEGER, c2 INTEGER); 88 INSERT INTO t1 VALUES(1, 1, 1), (1, 1, 1); 89 SELECT CASE AVG (c0) WHEN any_value(c1) * any_value(c2) THEN 1 END FROM t1; 90 CASE AVG (c0) WHEN any_value(c1) * any_value(c2) THEN 1 END 91 1 92 SELECT CASE any_value(c1) * any_value(c2) WHEN SUM(c0) THEN 1 WHEN AVG(c0) THEN 2 END FROM t1; 93 CASE any_value(c1) * any_value(c2) WHEN SUM(c0) THEN 1 WHEN AVG(c0) THEN 2 END 94 2 95 SELECT CASE any_value(c1) WHEN any_value(c1) + 1 THEN 1 END, ABS(AVG(c0)) FROM t1; 96 CASE any_value(c1) WHEN any_value(c1) + 1 THEN 1 END ABS(AVG(c0)) 97 null 1.0000 98 DROP TABLE t1; 99 CREATE TABLE t1(a varchar(255), b INT, c INT UNSIGNED, d DECIMAL(12,2), e REAL); 100 INSERT INTO t1 VALUES('iynfj', 1, 1, 1, 1); 101 INSERT INTO t1 VALUES('innfj', 2, 2, 2, 2); 102 SELECT SUM( a ) FROM t1 GROUP BY a HAVING a IN ( AVG( 1 ), 1 + a); 103 invalid argument aggregate function sum, bad value [VARCHAR] 104 SELECT SUM( b ) FROM t1 GROUP BY b HAVING b IN ( AVG( 1 ), 1 + b); 105 SUM( b ) 106 1 107 SELECT SUM( c ) FROM t1 GROUP BY c HAVING c IN ( AVG( 1 ), 1 + c); 108 SUM( c ) 109 1 110 SELECT SUM( d ) FROM t1 GROUP BY d HAVING d IN ( AVG( 1 ), 1 + d); 111 SUM( d ) 112 1.00 113 SELECT SUM( e ) FROM t1 GROUP BY e HAVING e IN ( AVG( 1 ), 1 + e); 114 SUM( e ) 115 1.0 116 DROP TABLE t1; 117 SELECT SUM(NULL); 118 SUM(NULL) 119 null 120 select sum(1)>sum(0); 121 sum(1)>sum(0) 122 true 123 create table t1(a bigint, b float, c double, d double, e varchar(1000)); 124 insert into t1 select 21474836471, 12412490231412.124124124124, 124141231249124124.1241241243124123, 12421512141241241241241241849912840129402.1241124124241241, space(1000); 125 insert into t1 select 21474836471, 12412490231412.124124124124, 124141231249124124.1241241243124123, 12421512141241241241241241849912840129402.1241124124241241, space(1000); 126 select sum(a), sum(b),sum(c),sum(d),sum(e) from t1; 127 invalid argument aggregate function sum, bad value [VARCHAR] 128 select sum(a), sum(b),sum(c),sum(d) from t1; 129 sum(a) sum(b) sum(c) sum(d) 130 42949672942 2.4824980176896E13 2.48282462498248256E17 2.484302428248248E40 131 drop table t1; 132 create table t1 (num float(5,2),user char(20)); 133 insert into t1 values (10.3,'nem'),(20.53,'monty'),(30.23,'sinisa'); 134 insert into t1 values (30.13,'nem'),(20.98,'monty'),(10.45,'sinisa'); 135 insert into t1 values (5.2,'nem'),(8.64,'monty'),(11.12,'sinisa'); 136 select sum(num) from t1; 137 sum(num) 138 147.58 139 select sum(num) from t1 group by user; 140 sum(num) 141 45.63 142 50.15 143 51.8 144 drop table t1; 145 create table t2 (ff double); 146 insert into t2 values (2.2); 147 select cast(sum( ff) as decimal(5,2)) from t2; 148 cast(sum( ff) as decimal(5,2)) 149 2.20 150 select cast(sum( ff) as signed) from t2; 151 cast(sum( ff) as signed) 152 2 153 select cast(variance(ff) as decimal(10,3)) from t2; 154 cast(variance(ff) as decimal(10,3)) 155 0.000 156 select cast(min(ff) as decimal(5,2)) from t2; 157 cast(min(ff) as decimal(5,2)) 158 2.20 159 create table t1 (df decimal(5,1)); 160 insert into t1 values(1.1); 161 insert into t1 values(2.2); 162 select cast(sum( df) as signed) from t1; 163 cast(sum( df) as signed) 164 3 165 select cast(min(df) as signed) from t1; 166 cast(min(df) as signed) 167 1 168 select 1e8 * sum( df) from t1; 169 1e8 * sum( df) 170 3.3E8 171 select 1e8 * min(df) from t1; 172 1e8 * min(df) 173 1.1000000000000001E8 174 create table t3 (ifl int); 175 insert into t3 values(1), (2); 176 select cast(min(ifl) as decimal(5,2)) from t3; 177 cast(min(ifl) as decimal(5,2)) 178 1.00 179 drop table t1; 180 drop table t2; 181 drop table t3; 182 create table t1 (grp int, a bigint unsigned, c char(10) not null); 183 insert into t1 values (1,1,"a"); 184 insert into t1 values (2,2,"b"); 185 insert into t1 values (2,3,"c"); 186 insert into t1 values (3,4,"E"); 187 insert into t1 values (3,5,"C"); 188 insert into t1 values (3,6,"D"); 189 select sum(distinct a),sum(distinct grp) from t1; 190 sum(distinct a) sum(distinct grp) 191 21 6 192 insert into t1 values (null,null,''); 193 select sum(distinct a),sum(distinct grp) from t1; 194 sum(distinct a) sum(distinct grp) 195 21 6 196 drop table t1;