group.sql.out

关系型数据库 Postgresql 6.5.2

QUERY: create table G (x int4, y int4, z int4);
QUERY: insert into G values (1, 2, 6);
QUERY: insert into G values (1, 3, 7);
QUERY: insert into G values (1, 3, 8);
QUERY: insert into G values (1, 4, 9);
QUERY: insert into G values (1, 4, 10);
QUERY: insert into G values (1, 4, 11);
QUERY: insert into G values (1, 5, 12);
QUERY: insert into G values (1, 5, 13);
QUERY: select x from G group by x;
x  
-- 
1  
QUERY: select y from G group by y;
y  
-- 
2  
3  
4  
5  
QUERY: select z from G group by z;
z   
--- 
6   
7   
8   
9   
10  
11  
12  
13  
QUERY: select x, y from G group by x, y;
x  y  
-- -- 
1  2  
1  3  
1  4  
1  5  
QUERY: select x, y from G group by y, x;
x  y  
-- -- 
1  2  
1  3  
1  4  
1  5  
QUERY: select x, y, z from G group by x, y, z;
x  y  z   
-- -- --- 
1  2  6   
1  3  7   
1  3  8   
1  4  9   
1  4  10  
1  4  11  
1  5  12  
1  5  13  
QUERY: select count(y) from G group by y;
count  
------ 
1      
2      
3      
2      
QUERY: select x, count(x) from G group by x;
x  count  
-- ------ 
1  8      
QUERY: select y, count(y), sum(G.z) from G group by y;
y  count  sum  
-- ------ ---- 
2  1      6    
3  2      15   
4  3      30   
5  2      25   
QUERY: select sum(G.x), sum(G.y), z from G group by z;
sum  sum  z   
---- ---- --- 
1    2    6   
1    3    7   
1    3    8   
1    4    9   
1    4    10  
1    4    11  
1    5    12  
1    5    13  
QUERY: select y, avg(z) from G group by y;
y  avg  
-- ---- 
2  6    
3  7    
4  10   
5  12   
QUERY: select sum(x) from G group by y;
sum  
---- 
1    
2    
3    
2    
QUERY: select sum(x), sum(z) from G group by y;
sum  sum  
---- ---- 
1    6    
2    15   
3    30   
2    25   
QUERY: select sum(z) from G group by y;
sum  
---- 
6    
15   
30   
25   
QUERY: select sum(G.z)/count(G.z), avg(G.z) from G group by y;
?column?  avg  
--------- ---- 
6         6    
7         7    
10        10   
12        12   
QUERY: select y, count(y) from G where z < 11 group by y;
y  count  
-- ------ 
2  1      
3  2      
4  2      
QUERY: select y, count(y) from G where z > 9 group by y;
y  count  
-- ------ 
4  2      
5  2      
QUERY: select y, count(y) from G where z > 8 and z < 12 group by y;
y  count  
-- ------ 
4  3      
QUERY: select y, count(y) from G where y = 4 group by y;
y  count  
-- ------ 
4  3      
QUERY: select y, count(y) from G where y > 10 group by y;
y  count  
-- ------ 
   0      
QUERY: select y, count(y) as c from G group by y order by c;
y  c  
-- -- 
2  1  
5  2  
3  2  
4  3  
QUERY: select y, count(y) as c from G group by y order by c, y;
y  c  
-- -- 
2  1  
3  2  
5  2  
4  3  
QUERY: select y, count(y) as c from G where z > 20 group by y order by c;
y  c  
-- -- 
   0  
QUERY: select x, y from G order by y, x;
x  y  
-- -- 
1  2  
1  3  
1  3  
1  4  
1  4  
1  4  
1  5  
1  5  
QUERY: create table H (a int4, b int4);
QUERY: insert into H values (3, 9)
insert into H values (4, 13);
QUERY: create table F (p int4);
QUERY: insert into F values (7)
insert into F values (11);
QUERY: select y from G, H where G.y = H.a group by y;
y  
-- 
3  
4  
QUERY: select sum(b) from G, H where G.y = H.a group by y;
sum  
---- 
18   
39   
QUERY: select y, count(y), sum(b) from G, H where G.y = H.a group by y;
y  count  sum  
-- ------ ---- 
3  2      18   
4  3      39   
QUERY: select a, sum(x), sum(b) from G, H where G.y = H.a group by a;
a  sum  sum  
-- ---- ---- 
3  2    18   
4  3    39   
QUERY: select y, count(*) from G, H where G.z = H.b group by y;
y  count  
-- ------ 
4  1      
5  1      
QUERY: select z, sum(y) from G, H, F where G.y = H.a and G.z = F.p group by z;
z   sum  
--- ---- 
7   3    
11  4    
QUERY: select a, avg(p) from G, H, F where G.y = H.a and G.z = F.p group by a;
a  avg  
-- ---- 
3  7    
4  11   
QUERY: select sum(x) from G, H where G.y = H.a;
sum  
---- 
5    
QUERY: select sum(y) from G, H where G.y = H.a;
sum  
---- 
18   
QUERY: select sum(a) from G, H where G.y = H.a;
sum  
---- 
18   
QUERY: select sum(b) from G, H where G.y = H.a;
sum  
---- 
57   
QUERY: select count(*) from G group by y;
count  
------ 
1      
2      
3      
2      
QUERY: insert into G (y, z) values (6, 14);
QUERY: insert into G (x, z) values (2, 14);
QUERY: select count(*) from G;
count  
------ 
10     
QUERY: select count(x), count(y), count(z) from G;
count  count  count  
------ ------ ------ 
9      9      10     
QUERY: select x from G group by x;
x  
-- 
1  
2  
   
QUERY: select y, count(*) from G group by y;
y  count  
-- ------ 
2  1      
3  2      
4  3      
5  2      
6  1      
   1      
QUERY: drop table G, H, F;