4
SELECT avg(four) AS avg_1 FROM onek;
10
SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
16
-- In 7.1, avg(float4) is computed using float8 arithmetic.
17
-- Round the result to 3 digits to avoid platform-specific results.
18
SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest;
24
SELECT avg(gpa) AS avg_3_4 FROM ONLY student;
30
SELECT sum(four) AS sum_1500 FROM onek;
36
SELECT sum(a) AS sum_198 FROM aggtest;
42
SELECT sum(b) AS avg_431_773 FROM aggtest;
48
SELECT sum(gpa) AS avg_6_8 FROM ONLY student;
54
SELECT max(four) AS max_3 FROM onek;
60
SELECT max(a) AS max_100 FROM aggtest;
66
SELECT max(aggtest.b) AS max_324_78 FROM aggtest;
72
SELECT max(student.gpa) AS max_3_7 FROM student;
78
SELECT count(four) AS cnt_1000 FROM onek;
84
SELECT count(DISTINCT four) AS cnt_4 FROM onek;
90
select ten, count(*), sum(four) from onek
91
group by ten order by ten;
106
select ten, count(four), sum(DISTINCT four) from onek
107
group by ten order by ten;
122
SELECT newavg(four) AS avg_1 FROM onek;
128
SELECT newsum(four) AS sum_1500 FROM onek;
134
SELECT newcnt(four) AS cnt_1000 FROM onek;
140
-- test for outer-level aggregates
142
select ten, sum(distinct four) from onek a
144
having exists (select 1 from onek b where sum(distinct a.four) = b.four);
154
-- this should fail because subquery has an agg of its own in WHERE
155
select ten, sum(distinct four) from onek a
157
having exists (select 1 from onek b
158
where sum(distinct a.four + b.four) = b.four);
159
ERROR: aggregates not allowed in WHERE clause
161
-- test for bitwise integer aggregates
163
CREATE TEMPORARY TABLE bitwise_test(
181
COPY bitwise_test FROM STDIN NULL 'null';
188
BIT_AND(y) AS "0100",
196
1 | 1 | 1 | ? | 0 | 0100 | 7 | 7 | 7 | ? | 7 | 1101
197
---+---+---+---+---+------+---+---+---+---+---+------
198
1 | 1 | 1 | 1 | 0 | 0100 | 7 | 7 | 7 | 3 | 7 | 1101
202
-- test boolean aggregates
204
-- first test all possible transition and final states
206
-- boolean and transitions
207
-- null because strict
208
booland_statefunc(NULL, NULL) IS NULL AS "t",
209
booland_statefunc(TRUE, NULL) IS NULL AS "t",
210
booland_statefunc(FALSE, NULL) IS NULL AS "t",
211
booland_statefunc(NULL, TRUE) IS NULL AS "t",
212
booland_statefunc(NULL, FALSE) IS NULL AS "t",
213
-- and actual computations
214
booland_statefunc(TRUE, TRUE) AS "t",
215
NOT booland_statefunc(TRUE, FALSE) AS "t",
216
NOT booland_statefunc(FALSE, TRUE) AS "t",
217
NOT booland_statefunc(FALSE, FALSE) AS "t";
218
t | t | t | t | t | t | t | t | t
219
---+---+---+---+---+---+---+---+---
220
t | t | t | t | t | t | t | t | t
224
-- boolean or transitions
225
-- null because strict
226
boolor_statefunc(NULL, NULL) IS NULL AS "t",
227
boolor_statefunc(TRUE, NULL) IS NULL AS "t",
228
boolor_statefunc(FALSE, NULL) IS NULL AS "t",
229
boolor_statefunc(NULL, TRUE) IS NULL AS "t",
230
boolor_statefunc(NULL, FALSE) IS NULL AS "t",
231
-- actual computations
232
boolor_statefunc(TRUE, TRUE) AS "t",
233
boolor_statefunc(TRUE, FALSE) AS "t",
234
boolor_statefunc(FALSE, TRUE) AS "t",
235
NOT boolor_statefunc(FALSE, FALSE) AS "t";
236
t | t | t | t | t | t | t | t | t
237
---+---+---+---+---+---+---+---+---
238
t | t | t | t | t | t | t | t | t
241
CREATE TEMPORARY TABLE bool_test(
256
COPY bool_test FROM STDIN NULL 'null';
262
BOOL_AND(NOT b2) AS "f",
263
BOOL_AND(NOT b3) AS "t"
265
f | t | f | n | f | t
266
---+---+---+---+---+---
275
EVERY(NOT b2) AS "f",
278
f | t | f | n | f | t
279
---+---+---+---+---+---
288
BOOL_OR(NOT b2) AS "f",
289
BOOL_OR(NOT b3) AS "t"
291
t | t | f | n | f | t
292
---+---+---+---+---+---