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 stddev_pop(b) FROM aggtest;
84 SELECT stddev_samp(b) FROM aggtest;
90 SELECT var_pop(b) FROM aggtest;
96 SELECT var_samp(b) FROM aggtest;
102 SELECT stddev_pop(b::numeric) FROM aggtest;
108 SELECT stddev_samp(b::numeric) FROM aggtest;
114 SELECT var_pop(b::numeric) FROM aggtest;
120 SELECT var_samp(b::numeric) FROM aggtest;
126 -- population variance is defined for a single tuple, sample variance
128 SELECT var_pop(1.0), var_samp(2.0);
134 SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric);
135 stddev_pop | stddev_samp
136 ------------+-------------
140 -- SQL2003 binary aggregates
141 SELECT regr_count(b, a) FROM aggtest;
147 SELECT regr_sxx(b, a) FROM aggtest;
153 SELECT regr_syy(b, a) FROM aggtest;
159 SELECT regr_sxy(b, a) FROM aggtest;
165 SELECT regr_avgx(b, a), regr_avgy(b, a) FROM aggtest;
166 regr_avgx | regr_avgy
167 -----------+------------------
168 49.5 | 107.943152273074
171 SELECT regr_r2(b, a) FROM aggtest;
177 SELECT regr_slope(b, a), regr_intercept(b, a) FROM aggtest;
178 regr_slope | regr_intercept
179 -------------------+------------------
180 0.512750700441271 | 82.5619926012309
183 SELECT covar_pop(b, a), covar_samp(b, a) FROM aggtest;
184 covar_pop | covar_samp
185 -----------------+------------------
186 653.62895538751 | 871.505273850014
189 SELECT corr(b, a) FROM aggtest;
195 SELECT count(four) AS cnt_1000 FROM onek;
201 SELECT count(DISTINCT four) AS cnt_4 FROM onek;
207 select ten, count(*), sum(four) from onek
208 group by ten order by ten;
223 select ten, count(four), sum(DISTINCT four) from onek
224 group by ten order by ten;
239 -- user-defined aggregates
240 SELECT newavg(four) AS avg_1 FROM onek;
246 SELECT newsum(four) AS sum_1500 FROM onek;
252 SELECT newcnt(four) AS cnt_1000 FROM onek;
258 SELECT newcnt(*) AS cnt_1000 FROM onek;
264 SELECT oldcnt(*) AS cnt_1000 FROM onek;
270 SELECT sum2(q1,q2) FROM int8_tbl;
276 -- test for outer-level aggregates
278 select ten, sum(distinct four) from onek a
280 having exists (select 1 from onek b where sum(distinct a.four) = b.four);
290 -- this should fail because subquery has an agg of its own in WHERE
291 select ten, sum(distinct four) from onek a
293 having exists (select 1 from onek b
294 where sum(distinct a.four + b.four) = b.four);
295 ERROR: aggregates not allowed in WHERE clause
297 -- test for bitwise integer aggregates
299 CREATE TEMPORARY TABLE bitwise_test(
317 COPY bitwise_test FROM STDIN NULL 'null';
324 BIT_AND(y) AS "0100",
332 1 | 1 | 1 | ? | 0 | 0100 | 7 | 7 | 7 | ? | 7 | 1101
333 ---+---+---+---+---+------+---+---+---+---+---+------
334 1 | 1 | 1 | 1 | 0 | 0100 | 7 | 7 | 7 | 3 | 7 | 1101
338 -- test boolean aggregates
340 -- first test all possible transition and final states
342 -- boolean and transitions
343 -- null because strict
344 booland_statefunc(NULL, NULL) IS NULL AS "t",
345 booland_statefunc(TRUE, NULL) IS NULL AS "t",
346 booland_statefunc(FALSE, NULL) IS NULL AS "t",
347 booland_statefunc(NULL, TRUE) IS NULL AS "t",
348 booland_statefunc(NULL, FALSE) IS NULL AS "t",
349 -- and actual computations
350 booland_statefunc(TRUE, TRUE) AS "t",
351 NOT booland_statefunc(TRUE, FALSE) AS "t",
352 NOT booland_statefunc(FALSE, TRUE) AS "t",
353 NOT booland_statefunc(FALSE, FALSE) AS "t";
354 t | t | t | t | t | t | t | t | t
355 ---+---+---+---+---+---+---+---+---
356 t | t | t | t | t | t | t | t | t
360 -- boolean or transitions
361 -- null because strict
362 boolor_statefunc(NULL, NULL) IS NULL AS "t",
363 boolor_statefunc(TRUE, NULL) IS NULL AS "t",
364 boolor_statefunc(FALSE, NULL) IS NULL AS "t",
365 boolor_statefunc(NULL, TRUE) IS NULL AS "t",
366 boolor_statefunc(NULL, FALSE) IS NULL AS "t",
367 -- actual computations
368 boolor_statefunc(TRUE, TRUE) AS "t",
369 boolor_statefunc(TRUE, FALSE) AS "t",
370 boolor_statefunc(FALSE, TRUE) AS "t",
371 NOT boolor_statefunc(FALSE, FALSE) AS "t";
372 t | t | t | t | t | t | t | t | t
373 ---+---+---+---+---+---+---+---+---
374 t | t | t | t | t | t | t | t | t
377 CREATE TEMPORARY TABLE bool_test(
392 COPY bool_test FROM STDIN NULL 'null';
398 BOOL_AND(NOT b2) AS "f",
399 BOOL_AND(NOT b3) AS "t"
401 f | t | f | n | f | t
402 ---+---+---+---+---+---
411 EVERY(NOT b2) AS "f",
414 f | t | f | n | f | t
415 ---+---+---+---+---+---
424 BOOL_OR(NOT b2) AS "f",
425 BOOL_OR(NOT b3) AS "t"
427 t | t | f | n | f | t
428 ---+---+---+---+---+---
433 -- Test several cases that should be optimized into indexscans instead of
434 -- the generic aggregate implementation. We can't actually verify that they
435 -- are done as indexscans, but we can check that the results are correct.
438 select max(unique1) from tenk1;
444 select max(unique1) from tenk1 where unique1 < 42;
450 select max(unique1) from tenk1 where unique1 > 42;
456 select max(unique1) from tenk1 where unique1 > 42000;
462 -- multi-column index (uses tenk1_thous_tenthous)
463 select max(tenthous) from tenk1 where thousand = 33;
469 select min(tenthous) from tenk1 where thousand = 33;
475 -- check parameter propagation into an indexscan subquery
476 select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt