aggregates.out 2.79 KB
Newer Older
1 2 3 4
--
-- AGGREGATES
--
SELECT avg(four) AS avg_1 FROM onek;
5 6 7
       avg_1        
--------------------
 1.5000000000000000
8 9
(1 row)

10
SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
11 12 13
       avg_32        
---------------------
 32.6666666666666667
14 15
(1 row)

16 17 18
-- In 7.1, avg(float4) is computed using float8 arithmetic.
-- Round the result to 3 digits to avoid platform-specific results.
SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest;
19 20 21
 avg_107_943 
-------------
     107.943
22 23
(1 row)

24
SELECT avg(gpa) AS avg_3_4 FROM ONLY student;
25 26 27
 avg_3_4 
---------
     3.4
28 29
(1 row)

30 31 32 33
SELECT sum(four) AS sum_1500 FROM onek;
 sum_1500 
----------
     1500
34 35
(1 row)

36 37 38 39 40 41 42 43 44 45
SELECT sum(a) AS sum_198 FROM aggtest;
 sum_198 
---------
     198
(1 row)

SELECT sum(b) AS avg_431_773 FROM aggtest;
 avg_431_773 
-------------
     431.773
46 47
(1 row)

48
SELECT sum(gpa) AS avg_6_8 FROM ONLY student;
49 50 51
 avg_6_8 
---------
     6.8
52 53
(1 row)

54 55
SELECT max(four) AS max_3 FROM onek;
 max_3 
56
-------
57
     3
58 59
(1 row)

60 61 62 63
SELECT max(a) AS max_100 FROM aggtest;
 max_100 
---------
     100
64 65
(1 row)

66 67 68 69
SELECT max(aggtest.b) AS max_324_78 FROM aggtest;
 max_324_78 
------------
     324.78
70 71
(1 row)

72 73 74 75
SELECT max(student.gpa) AS max_3_7 FROM student;
 max_3_7 
---------
     3.7
76 77
(1 row)

78 79 80 81
SELECT count(four) AS cnt_1000 FROM onek;
 cnt_1000 
----------
     1000
82 83
(1 row)

84 85 86 87 88 89
SELECT count(DISTINCT four) AS cnt_4 FROM onek;
 cnt_4 
-------
     4
(1 row)

90 91
select ten, count(*), sum(four) from onek
group by ten order by ten;
92 93 94 95 96 97 98 99 100 101 102 103
 ten | count | sum 
-----+-------+-----
   0 |   100 | 100
   1 |   100 | 200
   2 |   100 | 100
   3 |   100 | 200
   4 |   100 | 100
   5 |   100 | 200
   6 |   100 | 100
   7 |   100 | 200
   8 |   100 | 100
   9 |   100 | 200
104 105
(10 rows)

106 107
select ten, count(four), sum(DISTINCT four) from onek
group by ten order by ten;
108 109 110 111 112 113 114 115 116 117 118 119
 ten | count | sum 
-----+-------+-----
   0 |   100 |   2
   1 |   100 |   4
   2 |   100 |   2
   3 |   100 |   4
   4 |   100 |   2
   5 |   100 |   4
   6 |   100 |   2
   7 |   100 |   4
   8 |   100 |   2
   9 |   100 |   4
120 121
(10 rows)

122
SELECT newavg(four) AS avg_1 FROM onek;
123 124 125
       avg_1        
--------------------
 1.5000000000000000
126 127
(1 row)

128 129 130 131
SELECT newsum(four) AS sum_1500 FROM onek;
 sum_1500 
----------
     1500
132 133
(1 row)

134 135 136 137
SELECT newcnt(four) AS cnt_1000 FROM onek;
 cnt_1000 
----------
     1000
138 139
(1 row)

140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158
-- test for outer-level aggregates
-- this should work
select ten, sum(distinct four) from onek a
group by ten
having exists (select 1 from onek b where sum(distinct a.four) = b.four);
 ten | sum 
-----+-----
   0 |   2
   2 |   2
   4 |   2
   6 |   2
   8 |   2
(5 rows)

-- this should fail because subquery has an agg of its own in WHERE
select ten, sum(distinct four) from onek a
group by ten
having exists (select 1 from onek b
               where sum(distinct a.four + b.four) = b.four);
159
ERROR:  aggregates not allowed in WHERE clause