func_group.result 4.06 KB
Newer Older
1
drop table if exists t1,t2;
unknown's avatar
unknown committed
2 3 4 5 6 7 8 9
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;
10 11 12 13 14 15 16
a	c	sum(a)
1	a	1
2	b	2
3	c	3
4	E	4
5	C	5
6	D	6
unknown's avatar
unknown committed
17
select a,c,sum(a) from t1 where a > 10 group by a;
18
a	c	sum(a)
unknown's avatar
unknown committed
19
select sum(a) from t1 where a > 10;
20 21
sum(a)
NULL
unknown's avatar
unknown committed
22
select a from t1 order by rand(10);
23
a
24 25
2
6
26 27 28 29
1
3
5
4
unknown's avatar
unknown committed
30
select distinct a from t1 order by rand(10);
31
a
32 33
2
6
34 35 36 37
1
3
5
4
unknown's avatar
unknown committed
38
select count(distinct a),count(distinct grp) from t1;
39 40
count(distinct a)	count(distinct grp)
6	3
unknown's avatar
unknown committed
41 42
insert into t1 values (null,null,'');
select count(distinct a),count(distinct grp) from t1;
43 44
count(distinct a)	count(distinct grp)
6	3
unknown's avatar
unknown committed
45 46 47 48 49 50 51 52 53 54
select sum(a),count(a),avg(a),std(a),variance(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)	variance(a)	bit_or(a)	bit_and(a)	min(a)	max(a)	min(c)	max(c)
21	6	3.5000	1.7078	2.9167	7	0	1	6		E
select grp, sum(a),count(a),avg(a),std(a),variance(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)	variance(a)	bit_or(a)	bit_and(a)	min(a)	max(a)	min(c)	max(c)
NULL	0	0	NULL	NULL	NULL	0	0	NULL	NULL		
1	1	1	1.0000	0.0000	0.0000	1	1	1	1	a	a
2	5	2	2.5000	0.5000	0.2500	3	2	2	3	b	c
3	15	3	5.0000	0.8165	0.6667	7	4	4	6	C	E
select grp, sum(a)+count(a)+avg(a)+std(a)+variance(a)+bit_or(a)+bit_and(a)+min(a)+max(a)+min(c)+max(c) as sum from t1 group by grp;
55 56 57
grp	sum
NULL	NULL
1	7
unknown's avatar
unknown committed
58 59
2	20.25
3	45.483163247594
unknown's avatar
unknown committed
60 61 62 63
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;
64 65 66 67 68 69
grp	a	c
NULL	NULL	
1	2	a
2	5	c
3	9	E
2	3	c
unknown's avatar
unknown committed
70 71 72 73 74
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');
unknown's avatar
unknown committed
75 76 77 78 79 80 81 82
select id, avg(value1), std(value1), variance(value1) from t1 group by id;
id	avg(value1)	std(value1)	variance(value1)
1	1.000000	0.816497	0.666667
2	11.000000	0.816497	0.666667
select name, avg(value1), std(value1), variance(value1) from t1, t2 where t1.id = t2.id group by t1.id;
name	avg(value1)	std(value1)	variance(value1)
Set One	1.000000	0.816497	0.666667
Set Two	11.000000	0.816497	0.666667
unknown's avatar
unknown committed
83 84 85 86 87 88
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;
89 90 91 92
id	avg(rating)
1	3.0000
2	NULL
3	2.0000
unknown's avatar
unknown committed
93 94 95 96 97 98
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;
99 100
count(*)
3
unknown's avatar
unknown committed
101
select count(*) from t1 where a = 1;
102 103
count(*)
1
unknown's avatar
unknown committed
104
select count(*) from t1 where a = 100;
105 106
count(*)
0
unknown's avatar
unknown committed
107
select count(*) from t1 where a >= 10;
108 109
count(*)
0
unknown's avatar
unknown committed
110
select count(a) from t1 where a = 1;
111 112
count(a)
1
unknown's avatar
unknown committed
113
select count(a) from t1 where a = 100;
114 115
count(a)
0
unknown's avatar
unknown committed
116
select count(a) from t1 where a >= 10;
117 118
count(a)
0
unknown's avatar
unknown committed
119
select count(b) from t1 where b >= 2;
120 121
count(b)
2
unknown's avatar
unknown committed
122
select count(b) from t1 where b >= 10;
123 124
count(b)
0
unknown's avatar
unknown committed
125
select count(c) from t1 where c = 10;
126 127
count(c)
0
unknown's avatar
unknown committed
128 129 130 131
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;
132 133
COUNT(i)	i	COUNT(i)*i
1	1	1
unknown's avatar
unknown committed
134
SELECT COUNT(i), (i+0), COUNT(i)*(i+0) FROM t1 GROUP BY i;
135 136
COUNT(i)	(i+0)	COUNT(i)*(i+0)
1	1	1
unknown's avatar
unknown committed
137 138 139 140 141 142 143 144 145
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;
146 147
sum(num)
147.58
unknown's avatar
unknown committed
148
select sum(num) from t1 group by user;
149 150 151 152
sum(num)
50.15
45.63
51.80
unknown's avatar
unknown committed
153
drop table t1;