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
296 LINE 4: where sum(distinct a.four + b.four) = b.four)...
298 -- Test handling of sublinks within outer-level aggregates.
299 -- Per bug report from Daniel Grace.
301 (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
309 -- test for bitwise integer aggregates
311 CREATE TEMPORARY TABLE bitwise_test(
329 COPY bitwise_test FROM STDIN NULL 'null';
336 BIT_AND(y) AS "0100",
344 1 | 1 | 1 | ? | 0 | 0100 | 7 | 7 | 7 | ? | 7 | 1101
345 ---+---+---+---+---+------+---+---+---+---+---+------
346 1 | 1 | 1 | 1 | 0 | 0100 | 7 | 7 | 7 | 3 | 7 | 1101
350 -- test boolean aggregates
352 -- first test all possible transition and final states
354 -- boolean and transitions
355 -- null because strict
356 booland_statefunc(NULL, NULL) IS NULL AS "t",
357 booland_statefunc(TRUE, NULL) IS NULL AS "t",
358 booland_statefunc(FALSE, NULL) IS NULL AS "t",
359 booland_statefunc(NULL, TRUE) IS NULL AS "t",
360 booland_statefunc(NULL, FALSE) IS NULL AS "t",
361 -- and actual computations
362 booland_statefunc(TRUE, TRUE) AS "t",
363 NOT booland_statefunc(TRUE, FALSE) AS "t",
364 NOT booland_statefunc(FALSE, TRUE) AS "t",
365 NOT booland_statefunc(FALSE, FALSE) AS "t";
366 t | t | t | t | t | t | t | t | t
367 ---+---+---+---+---+---+---+---+---
368 t | t | t | t | t | t | t | t | t
372 -- boolean or transitions
373 -- null because strict
374 boolor_statefunc(NULL, NULL) IS NULL AS "t",
375 boolor_statefunc(TRUE, NULL) IS NULL AS "t",
376 boolor_statefunc(FALSE, NULL) IS NULL AS "t",
377 boolor_statefunc(NULL, TRUE) IS NULL AS "t",
378 boolor_statefunc(NULL, FALSE) IS NULL AS "t",
379 -- actual computations
380 boolor_statefunc(TRUE, TRUE) AS "t",
381 boolor_statefunc(TRUE, FALSE) AS "t",
382 boolor_statefunc(FALSE, TRUE) AS "t",
383 NOT boolor_statefunc(FALSE, FALSE) AS "t";
384 t | t | t | t | t | t | t | t | t
385 ---+---+---+---+---+---+---+---+---
386 t | t | t | t | t | t | t | t | t
389 CREATE TEMPORARY TABLE bool_test(
404 COPY bool_test FROM STDIN NULL 'null';
410 BOOL_AND(NOT b2) AS "f",
411 BOOL_AND(NOT b3) AS "t"
413 f | t | f | n | f | t
414 ---+---+---+---+---+---
423 EVERY(NOT b2) AS "f",
426 f | t | f | n | f | t
427 ---+---+---+---+---+---
436 BOOL_OR(NOT b2) AS "f",
437 BOOL_OR(NOT b3) AS "t"
439 t | t | f | n | f | t
440 ---+---+---+---+---+---
445 -- Test several cases that should be optimized into indexscans instead of
446 -- the generic aggregate implementation. We can't actually verify that they
447 -- are done as indexscans, but we can check that the results are correct.
450 select max(unique1) from tenk1;
456 select max(unique1) from tenk1 where unique1 < 42;
462 select max(unique1) from tenk1 where unique1 > 42;
468 select max(unique1) from tenk1 where unique1 > 42000;
474 -- multi-column index (uses tenk1_thous_tenthous)
475 select max(tenthous) from tenk1 where thousand = 33;
481 select min(tenthous) from tenk1 where thousand = 33;
487 -- check parameter propagation into an indexscan subquery
488 select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
499 -- check some cases that were handled incorrectly in 8.3.0
500 select distinct max(unique2) from tenk1;
506 select max(unique2) from tenk1 order by 1;
512 select max(unique2) from tenk1 order by max(unique2);
518 select max(unique2) from tenk1 order by max(unique2)+1;
524 select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
533 -- Test combinations of DISTINCT and/or ORDER BY
535 select array_agg(a order by b)
536 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
542 select array_agg(a order by a)
543 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
549 select array_agg(a order by a desc)
550 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
556 select array_agg(b order by a desc)
557 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
563 select array_agg(distinct a)
564 from (values (1),(2),(1),(3),(null),(2)) v(a);
570 select array_agg(distinct a order by a)
571 from (values (1),(2),(1),(3),(null),(2)) v(a);
577 select array_agg(distinct a order by a desc)
578 from (values (1),(2),(1),(3),(null),(2)) v(a);
584 select array_agg(distinct a order by a desc nulls last)
585 from (values (1),(2),(1),(3),(null),(2)) v(a);
591 -- multi-arg aggs, strict/nonstrict, distinct/order by
592 select aggfstr(a,b,c)
593 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
595 ---------------------------------------
596 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
600 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
602 -----------------------------------------------
603 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
606 select aggfstr(distinct a,b,c)
607 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
608 generate_series(1,3) i;
610 ---------------------------------------
611 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
614 select aggfns(distinct a,b,c)
615 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
616 generate_series(1,3) i;
618 -----------------------------------------------
619 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
622 select aggfstr(distinct a,b,c order by b)
623 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
624 generate_series(1,3) i;
626 ---------------------------------------
627 {"(3,1,baz)","(2,2,bar)","(1,3,foo)"}
630 select aggfns(distinct a,b,c order by b)
631 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
632 generate_series(1,3) i;
634 -----------------------------------------------
635 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
638 -- test specific code paths
639 select aggfns(distinct a,a,c order by c using ~<~,a)
640 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
641 generate_series(1,2) i;
643 ------------------------------------------------
644 {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"}
647 select aggfns(distinct a,a,c order by c using ~<~)
648 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
649 generate_series(1,2) i;
651 ------------------------------------------------
652 {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"}
655 select aggfns(distinct a,a,c order by a)
656 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
657 generate_series(1,2) i;
659 ------------------------------------------------
660 {"(0,0,)","(1,1,foo)","(2,2,bar)","(3,3,baz)"}
663 select aggfns(distinct a,b,c order by a,c using ~<~,b)
664 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
665 generate_series(1,2) i;
667 -----------------------------------------------
668 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
671 -- check node I/O via view creation and usage, also deparsing logic
672 create view agg_view1 as
674 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
675 select * from agg_view1;
677 -----------------------------------------------
678 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
681 select pg_get_viewdef('agg_view1'::regclass);
683 --------------------------------------------------------------------------------------------------------------------------------------------------------
684 SELECT aggfns(v.a, v.b, v.c) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
687 create or replace view agg_view1 as
688 select aggfns(distinct a,b,c)
689 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
690 generate_series(1,3) i;
691 select * from agg_view1;
693 -----------------------------------------------
694 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
697 select pg_get_viewdef('agg_view1'::regclass);
699 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
700 SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i);
703 create or replace view agg_view1 as
704 select aggfns(distinct a,b,c order by b)
705 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
706 generate_series(1,3) i;
707 select * from agg_view1;
709 -----------------------------------------------
710 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
713 select pg_get_viewdef('agg_view1'::regclass);
715 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
716 SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i);
719 create or replace view agg_view1 as
720 select aggfns(a,b,c order by b+1)
721 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
722 select * from agg_view1;
724 -----------------------------------------------
725 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
728 select pg_get_viewdef('agg_view1'::regclass);
730 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
731 SELECT aggfns(v.a, v.b, v.c ORDER BY (v.b + 1)) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
734 create or replace view agg_view1 as
735 select aggfns(a,a,c order by b)
736 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
737 select * from agg_view1;
739 ------------------------------------------------
740 {"(3,3,baz)","(2,2,bar)","(1,1,foo)","(0,0,)"}
743 select pg_get_viewdef('agg_view1'::regclass);
745 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
746 SELECT aggfns(v.a, v.a, v.c ORDER BY v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
749 create or replace view agg_view1 as
750 select aggfns(a,b,c order by c using ~<~)
751 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
752 select * from agg_view1;
754 -----------------------------------------------
755 {"(2,2,bar)","(3,1,baz)","(1,3,foo)","(0,,)"}
758 select pg_get_viewdef('agg_view1'::regclass);
760 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
761 SELECT aggfns(v.a, v.b, v.c ORDER BY v.c USING ~<~ NULLS LAST) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
764 create or replace view agg_view1 as
765 select aggfns(distinct a,b,c order by a,c using ~<~,b)
766 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
767 generate_series(1,2) i;
768 select * from agg_view1;
770 -----------------------------------------------
771 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
774 select pg_get_viewdef('agg_view1'::regclass);
776 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
777 SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 2) i(i);
781 -- incorrect DISTINCT usage errors
782 select aggfns(distinct a,b,c order by i)
783 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
784 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
785 LINE 1: select aggfns(distinct a,b,c order by i)
787 select aggfns(distinct a,b,c order by a,b+1)
788 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
789 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
790 LINE 1: select aggfns(distinct a,b,c order by a,b+1)
792 select aggfns(distinct a,b,c order by a,b,i,c)
793 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
794 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
795 LINE 1: select aggfns(distinct a,b,c order by a,b,i,c)
797 select aggfns(distinct a,a,c order by a,b)
798 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
799 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
800 LINE 1: select aggfns(distinct a,a,c order by a,b)
803 select string_agg(a) from (values('aaaa'),('bbbb'),('cccc')) g(a);
809 select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a);
815 select string_agg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a);
821 select string_agg(a,',') from (values(null),(null),('bbbb'),('cccc')) g(a);
827 select string_agg(a,',') from (values(null),(null)) g(a);
833 -- check some implicit casting cases, as per bug #5564
834 select string_agg(distinct f1 order by f1) from varchar_tbl; -- ok
840 select string_agg(distinct f1::text order by f1) from varchar_tbl; -- not ok
841 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
842 LINE 1: select string_agg(distinct f1::text order by f1) from varcha...
844 select string_agg(distinct f1 order by f1::text) from varchar_tbl; -- not ok
845 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
846 LINE 1: select string_agg(distinct f1 order by f1::text) from varcha...
848 select string_agg(distinct f1::text order by f1::text) from varchar_tbl; -- ok