diff options
Diffstat (limited to 'mysql-test/r/func_group.result')
| -rw-r--r-- | mysql-test/r/func_group.result | 66 |
1 files changed, 66 insertions, 0 deletions
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index ea9a06123ae..bdb33be5d4c 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1,3 +1,11 @@ +create table t1 (grp int, a bigint unsigned, c char(10) not null); +insert into t1 values (1,1,"a"); +insert into t1 values (2,2,"b"); +insert into t1 values (2,3,"c"); +insert into t1 values (3,4,"E"); +insert into t1 values (3,5,"C"); +insert into t1 values (3,6,"D"); +select a,c,sum(a) from t1 group by a; a c sum(a) 1 a 1 2 b 2 @@ -5,9 +13,12 @@ a c sum(a) 4 E 4 5 C 5 6 D 6 +select a,c,sum(a) from t1 where a > 10 group by a; a c sum(a) +select sum(a) from t1 where a > 10; sum(a) NULL +select a from t1 order by rand(10); a 1 3 @@ -15,6 +26,7 @@ a 5 2 4 +select distinct a from t1 order by rand(10); a 1 3 @@ -22,65 +34,119 @@ a 5 2 4 +select count(distinct a),count(distinct grp) from t1; count(distinct a) count(distinct grp) 6 3 +insert into t1 values (null,null,''); +select count(distinct a),count(distinct grp) from t1; count(distinct a) count(distinct grp) 6 3 +select sum(a),count(a),avg(a),std(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1; sum(a) count(a) avg(a) std(a) bit_or(a) bit_and(a) min(a) max(a) min(c) max(c) 21 6 3.5000 1.7078 7 0 1 6 E +select grp, sum(a),count(a),avg(a),std(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp; grp sum(a) count(a) avg(a) std(a) bit_or(a) bit_and(a) min(a) max(a) min(c) max(c) NULL 0 0 NULL NULL 0 0 NULL NULL 1 1 1 1.0000 0.0000 1 1 1 1 a a 2 5 2 2.5000 0.5000 3 2 2 3 b c 3 15 3 5.0000 0.8165 7 4 4 6 C E +select grp, sum(a)+count(a)+avg(a)+std(a)+bit_or(a)+bit_and(a)+min(a)+max(a)+min(c)+max(c) as sum from t1 group by grp; grp sum NULL NULL 1 7 2 20 3 44.816496580928 +create table t2 (grp int, a bigint unsigned, c char(10)); +insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp; +replace into t2 select grp, a, c from t1 limit 2,1; +select * from t2; grp a c NULL NULL 1 2 a 2 5 c 3 9 E 2 3 c +drop table t1,t2; +CREATE TABLE t1 (id int(11),value1 float(10,2)); +INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00); +CREATE TABLE t2 (id int(11),name char(20)); +INSERT INTO t2 VALUES (1,'Set One'),(2,'Set Two'); +select id, avg(value1), std(value1) from t1 group by id; id avg(value1) std(value1) 1 1.000000 0.816497 2 11.000000 0.816497 +select name, avg(value1), std(value1) from t1, t2 where t1.id = t2.id group by t1.id; name avg(value1) std(value1) Set One 1.000000 0.816497 Set Two 11.000000 0.816497 +drop table t1,t2; +create table t1 (id int not null); +create table t2 (id int not null,rating int null); +insert into t1 values(1),(2),(3); +insert into t2 values(1, 3),(2, NULL),(2, NULL),(3, 2),(3, NULL); +select t1.id, avg(rating) from t1 left join t2 on ( t1.id = t2.id ) group by t1.id; id avg(rating) 1 3.0000 2 NULL 3 2.0000 +drop table t1,t2; +create table t1 (a smallint(6) primary key, c char(10), b text); +INSERT INTO t1 VALUES (1,'1','1'); +INSERT INTO t1 VALUES (2,'2','2'); +INSERT INTO t1 VALUES (4,'4','4'); +select count(*) from t1; count(*) 3 +select count(*) from t1 where a = 1; count(*) 1 +select count(*) from t1 where a = 100; count(*) 0 +select count(*) from t1 where a >= 10; count(*) 0 +select count(a) from t1 where a = 1; count(a) 1 +select count(a) from t1 where a = 100; count(a) 0 +select count(a) from t1 where a >= 10; count(a) 0 +select count(b) from t1 where b >= 2; count(b) 2 +select count(b) from t1 where b >= 10; count(b) 0 +select count(c) from t1 where c = 10; count(c) 0 +drop table t1; +CREATE TABLE t1 (d DATETIME, i INT); +INSERT INTO t1 VALUES (NOW(), 1); +SELECT COUNT(i), i, COUNT(i)*i FROM t1 GROUP BY i; COUNT(i) i COUNT(i)*i 1 1 1 +SELECT COUNT(i), (i+0), COUNT(i)*(i+0) FROM t1 GROUP BY i; COUNT(i) (i+0) COUNT(i)*(i+0) 1 1 1 +DROP TABLE t1; +create table t1 ( +num float(5,2), +user char(20) +); +insert into t1 values (10.3,'nem'),(20.53,'monty'),(30.23,'sinisa'); +insert into t1 values (30.13,'nem'),(20.98,'monty'),(10.45,'sinisa'); +insert into t1 values (5.2,'nem'),(8.64,'monty'),(11.12,'sinisa'); +select sum(num) from t1; sum(num) 147.58 +select sum(num) from t1 group by user; sum(num) 50.15 45.63 51.80 +drop table t1; |