1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
|
---------------------------------------------------------------------------
--
-- group.sql-
-- test GROUP BY (with aggregates)
--
--
-- Copyright (c) 1994-5, Regents of the University of California
--
-- $Id: group.sql,v 1.1.1.1 1996/07/09 06:22:30 scrappy Exp $
--
---------------------------------------------------------------------------
create table G (x int4, y int4, z int4);
insert into G values (1, 2, 6);
insert into G values (1, 3, 7);
insert into G values (1, 3, 8);
insert into G values (1, 4, 9);
insert into G values (1, 4, 10);
insert into G values (1, 4, 11);
insert into G values (1, 5, 12);
insert into G values (1, 5, 13);
select x from G group by x;
select y from G group by y;
select z from G group by z;
select x, y from G group by x, y;
select x, y from G group by y, x;
select x, y, z from G group by x, y, z;
-- mixed target list (aggregates and group columns)
select count(y) from G group by y;
select x, count(x) from G group by x;
select y, count(y), sum(G.z) from G group by y;
select sum(G.x), sum(G.y), z from G group by z;
select y, avg(z) from G group by y;
-- group attr not in target list
select sum(x) from G group by y;
select sum(x), sum(z) from G group by y;
select sum(z) from G group by y;
-- aggregates in expressions
select sum(G.z)/count(G.z), avg(G.z) from G group by y;
-- with qualifications
select y, count(y) from G where z < 11 group by y;
select y, count(y) from G where z > 9 group by y;
select y, count(y) from G where z > 8 and z < 12 group by y;
select y, count(y) from G where y = 4 group by y;
select y, count(y) from G where y > 10 group by y;
-- with order by
select y, count(y) as c from G group by y order by c;
select y, count(y) as c from G group by y order by c, y;
select y, count(y) as c from G where z > 20 group by y order by c;
-- just to make sure we didn't screw up order by
select x, y from G order by y, x;
-- with having
-- HAVING clause is not implemented yet
--select count(y) from G having count(y) > 1
--select count(y) from G group by y having y > 3
--select y from G group by y having y > 3
--select y from G where z > 10 group by y having y > 3
--select y from G group by y having y > 10
--select count(G.y) from G group by y having y > 10
--select y from G where z > 20 group by y having y > 3
create table H (a int4, b int4);
insert into H values (3, 9)
insert into H values (4, 13);
create table F (p int4);
insert into F values (7)
insert into F values (11);
-- joins
select y from G, H where G.y = H.a group by y;
select sum(b) from G, H where G.y = H.a group by y;
select y, count(y), sum(b) from G, H where G.y = H.a group by y;
select a, sum(x), sum(b) from G, H where G.y = H.a group by a;
select y, count(*) from G, H where G.z = H.b group by y;
select z, sum(y) from G, H, F where G.y = H.a and G.z = F.p group by z;
select a, avg(p) from G, H, F where G.y = H.a and G.z = F.p group by a;
-- just aggregates
select sum(x) from G, H where G.y = H.a;
select sum(y) from G, H where G.y = H.a;
select sum(a) from G, H where G.y = H.a;
select sum(b) from G, H where G.y = H.a;
select count(*) from G group by y;
insert into G (y, z) values (6, 14);
insert into G (x, z) values (2, 14);
select count(*) from G;
select count(x), count(y), count(z) from G;
select x from G group by x;
select y, count(*) from G group by y;
--
drop table G, H, F;
|
