1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.21. Aggregate Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-range.html" title="9.20. Range/Multirange Functions and Operators" /><link rel="next" href="functions-window.html" title="9.22. Window Functions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.21. Aggregate Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-range.html" title="9.20. Range/Multirange Functions and Operators">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-window.html" title="9.22. Window Functions">Next</a></td></tr></table><hr /></div><div class="sect1" id="FUNCTIONS-AGGREGATE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.21. Aggregate Functions <a href="#FUNCTIONS-AGGREGATE" class="id_link">#</a></h2></div></div></div><a id="id-1.5.8.27.2" class="indexterm"></a><p>
<em class="firstterm">Aggregate functions</em> compute a single result
from a set of input values. The built-in general-purpose aggregate
functions are listed in <a class="xref" href="functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE" title="Table 9.59. General-Purpose Aggregate Functions">Table 9.59</a>
while statistical aggregates are in <a class="xref" href="functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE" title="Table 9.60. Aggregate Functions for Statistics">Table 9.60</a>.
The built-in within-group ordered-set aggregate functions
are listed in <a class="xref" href="functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE" title="Table 9.61. Ordered-Set Aggregate Functions">Table 9.61</a>
while the built-in within-group hypothetical-set ones are in <a class="xref" href="functions-aggregate.html#FUNCTIONS-HYPOTHETICAL-TABLE" title="Table 9.62. Hypothetical-Set Aggregate Functions">Table 9.62</a>. Grouping operations,
which are closely related to aggregate functions, are listed in
<a class="xref" href="functions-aggregate.html#FUNCTIONS-GROUPING-TABLE" title="Table 9.63. Grouping Operations">Table 9.63</a>.
The special syntax considerations for aggregate
functions are explained in <a class="xref" href="sql-expressions.html#SYNTAX-AGGREGATES" title="4.2.7. Aggregate Expressions">Section 4.2.7</a>.
Consult <a class="xref" href="tutorial-agg.html" title="2.7. Aggregate Functions">Section 2.7</a> for additional introductory
information.
</p><p>
Aggregate functions that support <em class="firstterm">Partial Mode</em>
are eligible to participate in various optimizations, such as parallel
aggregation.
</p><div class="table" id="FUNCTIONS-AGGREGATE-TABLE"><p class="title"><strong>Table 9.59. General-Purpose Aggregate Functions</strong></p><div class="table-contents"><table class="table" summary="General-Purpose Aggregate Functions" border="1"><colgroup><col class="col1" /><col class="col2" /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
Function
</p>
<p>
Description
</p></th><th>Partial Mode</th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.1.1.1.1" class="indexterm"></a>
<code class="function">any_value</code> ( <code class="type">anyelement</code> )
→ <code class="returnvalue"><em class="replaceable"><code>same as input type</code></em></code>
</p>
<p>
Returns an arbitrary value from the non-null input values.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.2.1.1.1" class="indexterm"></a>
<code class="function">array_agg</code> ( <code class="type">anynonarray</code> )
→ <code class="returnvalue">anyarray</code>
</p>
<p>
Collects all the input values, including nulls, into an array.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">array_agg</code> ( <code class="type">anyarray</code> )
→ <code class="returnvalue">anyarray</code>
</p>
<p>
Concatenates all the input arrays into an array of one higher
dimension. (The inputs must all have the same dimensionality, and
cannot be empty or null.)
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.4.1.1.1" class="indexterm"></a>
<a id="id-1.5.8.27.5.2.4.4.1.1.2" class="indexterm"></a>
<code class="function">avg</code> ( <code class="type">smallint</code> )
→ <code class="returnvalue">numeric</code>
</p>
<p class="func_signature">
<code class="function">avg</code> ( <code class="type">integer</code> )
→ <code class="returnvalue">numeric</code>
</p>
<p class="func_signature">
<code class="function">avg</code> ( <code class="type">bigint</code> )
→ <code class="returnvalue">numeric</code>
</p>
<p class="func_signature">
<code class="function">avg</code> ( <code class="type">numeric</code> )
→ <code class="returnvalue">numeric</code>
</p>
<p class="func_signature">
<code class="function">avg</code> ( <code class="type">real</code> )
→ <code class="returnvalue">double precision</code>
</p>
<p class="func_signature">
<code class="function">avg</code> ( <code class="type">double precision</code> )
→ <code class="returnvalue">double precision</code>
</p>
<p class="func_signature">
<code class="function">avg</code> ( <code class="type">interval</code> )
→ <code class="returnvalue">interval</code>
</p>
<p>
Computes the average (arithmetic mean) of all the non-null input
values.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.5.1.1.1" class="indexterm"></a>
<code class="function">bit_and</code> ( <code class="type">smallint</code> )
→ <code class="returnvalue">smallint</code>
</p>
<p class="func_signature">
<code class="function">bit_and</code> ( <code class="type">integer</code> )
→ <code class="returnvalue">integer</code>
</p>
<p class="func_signature">
<code class="function">bit_and</code> ( <code class="type">bigint</code> )
→ <code class="returnvalue">bigint</code>
</p>
<p class="func_signature">
<code class="function">bit_and</code> ( <code class="type">bit</code> )
→ <code class="returnvalue">bit</code>
</p>
<p>
Computes the bitwise AND of all non-null input values.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.6.1.1.1" class="indexterm"></a>
<code class="function">bit_or</code> ( <code class="type">smallint</code> )
→ <code class="returnvalue">smallint</code>
</p>
<p class="func_signature">
<code class="function">bit_or</code> ( <code class="type">integer</code> )
→ <code class="returnvalue">integer</code>
</p>
<p class="func_signature">
<code class="function">bit_or</code> ( <code class="type">bigint</code> )
→ <code class="returnvalue">bigint</code>
</p>
<p class="func_signature">
<code class="function">bit_or</code> ( <code class="type">bit</code> )
→ <code class="returnvalue">bit</code>
</p>
<p>
Computes the bitwise OR of all non-null input values.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.7.1.1.1" class="indexterm"></a>
<code class="function">bit_xor</code> ( <code class="type">smallint</code> )
→ <code class="returnvalue">smallint</code>
</p>
<p class="func_signature">
<code class="function">bit_xor</code> ( <code class="type">integer</code> )
→ <code class="returnvalue">integer</code>
</p>
<p class="func_signature">
<code class="function">bit_xor</code> ( <code class="type">bigint</code> )
→ <code class="returnvalue">bigint</code>
</p>
<p class="func_signature">
<code class="function">bit_xor</code> ( <code class="type">bit</code> )
→ <code class="returnvalue">bit</code>
</p>
<p>
Computes the bitwise exclusive OR of all non-null input values.
Can be useful as a checksum for an unordered set of values.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.8.1.1.1" class="indexterm"></a>
<code class="function">bool_and</code> ( <code class="type">boolean</code> )
→ <code class="returnvalue">boolean</code>
</p>
<p>
Returns true if all non-null input values are true, otherwise false.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.9.1.1.1" class="indexterm"></a>
<code class="function">bool_or</code> ( <code class="type">boolean</code> )
→ <code class="returnvalue">boolean</code>
</p>
<p>
Returns true if any non-null input value is true, otherwise false.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.10.1.1.1" class="indexterm"></a>
<code class="function">count</code> ( <code class="literal">*</code> )
→ <code class="returnvalue">bigint</code>
</p>
<p>
Computes the number of input rows.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">count</code> ( <code class="type">"any"</code> )
→ <code class="returnvalue">bigint</code>
</p>
<p>
Computes the number of input rows in which the input value is not
null.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.12.1.1.1" class="indexterm"></a>
<code class="function">every</code> ( <code class="type">boolean</code> )
→ <code class="returnvalue">boolean</code>
</p>
<p>
This is the SQL standard's equivalent to <code class="function">bool_and</code>.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.13.1.1.1" class="indexterm"></a>
<code class="function">json_agg</code> ( <code class="type">anyelement</code> )
→ <code class="returnvalue">json</code>
</p>
<p class="func_signature">
<a id="id-1.5.8.27.5.2.4.13.1.2.1" class="indexterm"></a>
<code class="function">jsonb_agg</code> ( <code class="type">anyelement</code> )
→ <code class="returnvalue">jsonb</code>
</p>
<p>
Collects all the input values, including nulls, into a JSON array.
Values are converted to JSON as per <code class="function">to_json</code>
or <code class="function">to_jsonb</code>.
</p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.14.1.1.1" class="indexterm"></a>
<code class="function">json_objectagg</code> (
[<span class="optional"> { <em class="replaceable"><code>key_expression</code></em> { <code class="literal">VALUE</code> | ':' } <em class="replaceable"><code>value_expression</code></em> } </span>]
[<span class="optional"> { <code class="literal">NULL</code> | <code class="literal">ABSENT</code> } <code class="literal">ON NULL</code> </span>]
[<span class="optional"> { <code class="literal">WITH</code> | <code class="literal">WITHOUT</code> } <code class="literal">UNIQUE</code> [<span class="optional"> <code class="literal">KEYS</code> </span>] </span>]
[<span class="optional"> <code class="literal">RETURNING</code> <em class="replaceable"><code>data_type</code></em> [<span class="optional"> <code class="literal">FORMAT JSON</code> [<span class="optional"> <code class="literal">ENCODING UTF8</code> </span>] </span>] </span>])
</p>
<p>
Behaves like <code class="function">json_object</code>, but as an
aggregate function, so it only takes one
<em class="replaceable"><code>key_expression</code></em> and one
<em class="replaceable"><code>value_expression</code></em> parameter.
</p>
<p>
<code class="literal">SELECT json_objectagg(k:v) FROM (VALUES ('a'::text,current_date),('b',current_date + 1)) AS t(k,v)</code>
→ <code class="returnvalue">{ "a" : "2022-05-10", "b" : "2022-05-11" }</code>
</p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.15.1.1.1" class="indexterm"></a>
<code class="function">json_object_agg</code> ( <em class="parameter"><code>key</code></em>
<code class="type">"any"</code>, <em class="parameter"><code>value</code></em>
<code class="type">"any"</code> )
→ <code class="returnvalue">json</code>
</p>
<p class="func_signature">
<a id="id-1.5.8.27.5.2.4.15.1.2.1" class="indexterm"></a>
<code class="function">jsonb_object_agg</code> ( <em class="parameter"><code>key</code></em>
<code class="type">"any"</code>, <em class="parameter"><code>value</code></em>
<code class="type">"any"</code> )
→ <code class="returnvalue">jsonb</code>
</p>
<p>
Collects all the key/value pairs into a JSON object. Key arguments
are coerced to text; value arguments are converted as per
<code class="function">to_json</code> or <code class="function">to_jsonb</code>.
Values can be null, but keys cannot.
</p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.16.1.1.1" class="indexterm"></a>
<code class="function">json_object_agg_strict</code> (
<em class="parameter"><code>key</code></em> <code class="type">"any"</code>,
<em class="parameter"><code>value</code></em> <code class="type">"any"</code> )
→ <code class="returnvalue">json</code>
</p>
<p class="func_signature">
<a id="id-1.5.8.27.5.2.4.16.1.2.1" class="indexterm"></a>
<code class="function">jsonb_object_agg_strict</code> (
<em class="parameter"><code>key</code></em> <code class="type">"any"</code>,
<em class="parameter"><code>value</code></em> <code class="type">"any"</code> )
→ <code class="returnvalue">jsonb</code>
</p>
<p>
Collects all the key/value pairs into a JSON object. Key arguments
are coerced to text; value arguments are converted as per
<code class="function">to_json</code> or <code class="function">to_jsonb</code>.
The <em class="parameter"><code>key</code></em> can not be null. If the
<em class="parameter"><code>value</code></em> is null then the entry is skipped,
</p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.17.1.1.1" class="indexterm"></a>
<code class="function">json_object_agg_unique</code> (
<em class="parameter"><code>key</code></em> <code class="type">"any"</code>,
<em class="parameter"><code>value</code></em> <code class="type">"any"</code> )
→ <code class="returnvalue">json</code>
</p>
<p class="func_signature">
<a id="id-1.5.8.27.5.2.4.17.1.2.1" class="indexterm"></a>
<code class="function">jsonb_object_agg_unique</code> (
<em class="parameter"><code>key</code></em> <code class="type">"any"</code>,
<em class="parameter"><code>value</code></em> <code class="type">"any"</code> )
→ <code class="returnvalue">jsonb</code>
</p>
<p>
Collects all the key/value pairs into a JSON object. Key arguments
are coerced to text; value arguments are converted as per
<code class="function">to_json</code> or <code class="function">to_jsonb</code>.
Values can be null, but keys cannot.
If there is a duplicate key an error is thrown.
</p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.18.1.1.1" class="indexterm"></a>
<code class="function">json_arrayagg</code> (
[<span class="optional"> <em class="replaceable"><code>value_expression</code></em> </span>]
[<span class="optional"> <code class="literal">ORDER BY</code> <em class="replaceable"><code>sort_expression</code></em> </span>]
[<span class="optional"> { <code class="literal">NULL</code> | <code class="literal">ABSENT</code> } <code class="literal">ON NULL</code> </span>]
[<span class="optional"> <code class="literal">RETURNING</code> <em class="replaceable"><code>data_type</code></em> [<span class="optional"> <code class="literal">FORMAT JSON</code> [<span class="optional"> <code class="literal">ENCODING UTF8</code> </span>] </span>] </span>])
</p>
<p>
Behaves in the same way as <code class="function">json_array</code>
but as an aggregate function so it only takes one
<em class="replaceable"><code>value_expression</code></em> parameter.
If <code class="literal">ABSENT ON NULL</code> is specified, any NULL
values are omitted.
If <code class="literal">ORDER BY</code> is specified, the elements will
appear in the array in that order rather than in the input order.
</p>
<p>
<code class="literal">SELECT json_arrayagg(v) FROM (VALUES(2),(1)) t(v)</code>
→ <code class="returnvalue">[2, 1]</code>
</p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.19.1.1.1" class="indexterm"></a>
<code class="function">json_object_agg_unique_strict</code> (
<em class="parameter"><code>key</code></em> <code class="type">"any"</code>,
<em class="parameter"><code>value</code></em> <code class="type">"any"</code> )
→ <code class="returnvalue">json</code>
</p>
<p class="func_signature">
<a id="id-1.5.8.27.5.2.4.19.1.2.1" class="indexterm"></a>
<code class="function">jsonb_object_agg_unique_strict</code> (
<em class="parameter"><code>key</code></em> <code class="type">"any"</code>,
<em class="parameter"><code>value</code></em> <code class="type">"any"</code> )
→ <code class="returnvalue">jsonb</code>
</p>
<p>
Collects all the key/value pairs into a JSON object. Key arguments
are coerced to text; value arguments are converted as per
<code class="function">to_json</code> or <code class="function">to_jsonb</code>.
The <em class="parameter"><code>key</code></em> can not be null. If the
<em class="parameter"><code>value</code></em> is null then the entry is skipped.
If there is a duplicate key an error is thrown.
</p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.20.1.1.1" class="indexterm"></a>
<code class="function">max</code> ( <em class="replaceable"><code>see text</code></em> )
→ <code class="returnvalue"><em class="replaceable"><code>same as input type</code></em></code>
</p>
<p>
Computes the maximum of the non-null input
values. Available for any numeric, string, date/time, or enum type,
as well as <code class="type">inet</code>, <code class="type">interval</code>,
<code class="type">money</code>, <code class="type">oid</code>, <code class="type">pg_lsn</code>,
<code class="type">tid</code>, <code class="type">xid8</code>,
and arrays of any of these types.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.21.1.1.1" class="indexterm"></a>
<code class="function">min</code> ( <em class="replaceable"><code>see text</code></em> )
→ <code class="returnvalue"><em class="replaceable"><code>same as input type</code></em></code>
</p>
<p>
Computes the minimum of the non-null input
values. Available for any numeric, string, date/time, or enum type,
as well as <code class="type">inet</code>, <code class="type">interval</code>,
<code class="type">money</code>, <code class="type">oid</code>, <code class="type">pg_lsn</code>,
<code class="type">tid</code>, <code class="type">xid8</code>,
and arrays of any of these types.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.22.1.1.1" class="indexterm"></a>
<code class="function">range_agg</code> ( <em class="parameter"><code>value</code></em>
<code class="type">anyrange</code> )
→ <code class="returnvalue">anymultirange</code>
</p>
<p class="func_signature">
<code class="function">range_agg</code> ( <em class="parameter"><code>value</code></em>
<code class="type">anymultirange</code> )
→ <code class="returnvalue">anymultirange</code>
</p>
<p>
Computes the union of the non-null input values.
</p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.23.1.1.1" class="indexterm"></a>
<code class="function">range_intersect_agg</code> ( <em class="parameter"><code>value</code></em>
<code class="type">anyrange</code> )
→ <code class="returnvalue">anyrange</code>
</p>
<p class="func_signature">
<code class="function">range_intersect_agg</code> ( <em class="parameter"><code>value</code></em>
<code class="type">anymultirange</code> )
→ <code class="returnvalue">anymultirange</code>
</p>
<p>
Computes the intersection of the non-null input values.
</p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.24.1.1.1" class="indexterm"></a>
<code class="function">json_agg_strict</code> ( <code class="type">anyelement</code> )
→ <code class="returnvalue">json</code>
</p>
<p class="func_signature">
<a id="id-1.5.8.27.5.2.4.24.1.2.1" class="indexterm"></a>
<code class="function">jsonb_agg_strict</code> ( <code class="type">anyelement</code> )
→ <code class="returnvalue">jsonb</code>
</p>
<p>
Collects all the input values, skipping nulls, into a JSON array.
Values are converted to JSON as per <code class="function">to_json</code>
or <code class="function">to_jsonb</code>.
</p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.25.1.1.1" class="indexterm"></a>
<code class="function">string_agg</code> ( <em class="parameter"><code>value</code></em>
<code class="type">text</code>, <em class="parameter"><code>delimiter</code></em> <code class="type">text</code> )
→ <code class="returnvalue">text</code>
</p>
<p class="func_signature">
<code class="function">string_agg</code> ( <em class="parameter"><code>value</code></em>
<code class="type">bytea</code>, <em class="parameter"><code>delimiter</code></em> <code class="type">bytea</code> )
→ <code class="returnvalue">bytea</code>
</p>
<p>
Concatenates the non-null input values into a string. Each value
after the first is preceded by the
corresponding <em class="parameter"><code>delimiter</code></em> (if it's not null).
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.26.1.1.1" class="indexterm"></a>
<code class="function">sum</code> ( <code class="type">smallint</code> )
→ <code class="returnvalue">bigint</code>
</p>
<p class="func_signature">
<code class="function">sum</code> ( <code class="type">integer</code> )
→ <code class="returnvalue">bigint</code>
</p>
<p class="func_signature">
<code class="function">sum</code> ( <code class="type">bigint</code> )
→ <code class="returnvalue">numeric</code>
</p>
<p class="func_signature">
<code class="function">sum</code> ( <code class="type">numeric</code> )
→ <code class="returnvalue">numeric</code>
</p>
<p class="func_signature">
<code class="function">sum</code> ( <code class="type">real</code> )
→ <code class="returnvalue">real</code>
</p>
<p class="func_signature">
<code class="function">sum</code> ( <code class="type">double precision</code> )
→ <code class="returnvalue">double precision</code>
</p>
<p class="func_signature">
<code class="function">sum</code> ( <code class="type">interval</code> )
→ <code class="returnvalue">interval</code>
</p>
<p class="func_signature">
<code class="function">sum</code> ( <code class="type">money</code> )
→ <code class="returnvalue">money</code>
</p>
<p>
Computes the sum of the non-null input values.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.5.2.4.27.1.1.1" class="indexterm"></a>
<code class="function">xmlagg</code> ( <code class="type">xml</code> )
→ <code class="returnvalue">xml</code>
</p>
<p>
Concatenates the non-null XML input values (see
<a class="xref" href="functions-xml.html#FUNCTIONS-XML-XMLAGG" title="9.15.1.7. xmlagg">Section 9.15.1.7</a>).
</p></td><td>No</td></tr></tbody></table></div></div><br class="table-break" /><p>
It should be noted that except for <code class="function">count</code>,
these functions return a null value when no rows are selected. In
particular, <code class="function">sum</code> of no rows returns null, not
zero as one might expect, and <code class="function">array_agg</code>
returns null rather than an empty array when there are no input
rows. The <code class="function">coalesce</code> function can be used to
substitute zero or an empty array for null when necessary.
</p><p>
The aggregate functions <code class="function">array_agg</code>,
<code class="function">json_agg</code>, <code class="function">jsonb_agg</code>,
<code class="function">json_agg_strict</code>, <code class="function">jsonb_agg_strict</code>,
<code class="function">json_object_agg</code>, <code class="function">jsonb_object_agg</code>,
<code class="function">json_object_agg_strict</code>, <code class="function">jsonb_object_agg_strict</code>,
<code class="function">json_object_agg_unique</code>, <code class="function">jsonb_object_agg_unique</code>,
<code class="function">json_object_agg_unique_strict</code>,
<code class="function">jsonb_object_agg_unique_strict</code>,
<code class="function">string_agg</code>,
and <code class="function">xmlagg</code>, as well as similar user-defined
aggregate functions, produce meaningfully different result values
depending on the order of the input values. This ordering is
unspecified by default, but can be controlled by writing an
<code class="literal">ORDER BY</code> clause within the aggregate call, as shown in
<a class="xref" href="sql-expressions.html#SYNTAX-AGGREGATES" title="4.2.7. Aggregate Expressions">Section 4.2.7</a>.
Alternatively, supplying the input values from a sorted subquery
will usually work. For example:
</p><pre class="screen">
SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
</pre><p>
Beware that this approach can fail if the outer query level contains
additional processing, such as a join, because that might cause the
subquery's output to be reordered before the aggregate is computed.
</p><div class="note"><h3 class="title">Note</h3><a id="id-1.5.8.27.8.1" class="indexterm"></a><a id="id-1.5.8.27.8.2" class="indexterm"></a><p>
The boolean aggregates <code class="function">bool_and</code> and
<code class="function">bool_or</code> correspond to the standard SQL aggregates
<code class="function">every</code> and <code class="function">any</code> or
<code class="function">some</code>.
<span class="productname">PostgreSQL</span>
supports <code class="function">every</code>, but not <code class="function">any</code>
or <code class="function">some</code>, because there is an ambiguity built into
the standard syntax:
</p><pre class="programlisting">
SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;
</pre><p>
Here <code class="function">ANY</code> can be considered either as introducing
a subquery, or as being an aggregate function, if the subquery
returns one row with a Boolean value.
Thus the standard name cannot be given to these aggregates.
</p></div><div class="note"><h3 class="title">Note</h3><p>
Users accustomed to working with other SQL database management
systems might be disappointed by the performance of the
<code class="function">count</code> aggregate when it is applied to the
entire table. A query like:
</p><pre class="programlisting">
SELECT count(*) FROM sometable;
</pre><p>
will require effort proportional to the size of the table:
<span class="productname">PostgreSQL</span> will need to scan either the
entire table or the entirety of an index that includes all rows in
the table.
</p></div><p>
<a class="xref" href="functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE" title="Table 9.60. Aggregate Functions for Statistics">Table 9.60</a> shows
aggregate functions typically used in statistical analysis.
(These are separated out merely to avoid cluttering the listing
of more-commonly-used aggregates.) Functions shown as
accepting <em class="replaceable"><code>numeric_type</code></em> are available for all
the types <code class="type">smallint</code>, <code class="type">integer</code>,
<code class="type">bigint</code>, <code class="type">numeric</code>, <code class="type">real</code>,
and <code class="type">double precision</code>.
Where the description mentions
<em class="parameter"><code>N</code></em>, it means the
number of input rows for which all the input expressions are non-null.
In all cases, null is returned if the computation is meaningless,
for example when <em class="parameter"><code>N</code></em> is zero.
</p><a id="id-1.5.8.27.11" class="indexterm"></a><a id="id-1.5.8.27.12" class="indexterm"></a><div class="table" id="FUNCTIONS-AGGREGATE-STATISTICS-TABLE"><p class="title"><strong>Table 9.60. Aggregate Functions for Statistics</strong></p><div class="table-contents"><table class="table" summary="Aggregate Functions for Statistics" border="1"><colgroup><col class="col1" /><col class="col2" /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
Function
</p>
<p>
Description
</p></th><th>Partial Mode</th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.1.1.1.1" class="indexterm"></a>
<a id="id-1.5.8.27.13.2.4.1.1.1.2" class="indexterm"></a>
<code class="function">corr</code> ( <em class="parameter"><code>Y</code></em> <code class="type">double precision</code>, <em class="parameter"><code>X</code></em> <code class="type">double precision</code> )
→ <code class="returnvalue">double precision</code>
</p>
<p>
Computes the correlation coefficient.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.2.1.1.1" class="indexterm"></a>
<a id="id-1.5.8.27.13.2.4.2.1.1.2" class="indexterm"></a>
<code class="function">covar_pop</code> ( <em class="parameter"><code>Y</code></em> <code class="type">double precision</code>, <em class="parameter"><code>X</code></em> <code class="type">double precision</code> )
→ <code class="returnvalue">double precision</code>
</p>
<p>
Computes the population covariance.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.3.1.1.1" class="indexterm"></a>
<a id="id-1.5.8.27.13.2.4.3.1.1.2" class="indexterm"></a>
<code class="function">covar_samp</code> ( <em class="parameter"><code>Y</code></em> <code class="type">double precision</code>, <em class="parameter"><code>X</code></em> <code class="type">double precision</code> )
→ <code class="returnvalue">double precision</code>
</p>
<p>
Computes the sample covariance.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.4.1.1.1" class="indexterm"></a>
<code class="function">regr_avgx</code> ( <em class="parameter"><code>Y</code></em> <code class="type">double precision</code>, <em class="parameter"><code>X</code></em> <code class="type">double precision</code> )
→ <code class="returnvalue">double precision</code>
</p>
<p>
Computes the average of the independent variable,
<code class="literal">sum(<em class="parameter"><code>X</code></em>)/<em class="parameter"><code>N</code></em></code>.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.5.1.1.1" class="indexterm"></a>
<code class="function">regr_avgy</code> ( <em class="parameter"><code>Y</code></em> <code class="type">double precision</code>, <em class="parameter"><code>X</code></em> <code class="type">double precision</code> )
→ <code class="returnvalue">double precision</code>
</p>
<p>
Computes the average of the dependent variable,
<code class="literal">sum(<em class="parameter"><code>Y</code></em>)/<em class="parameter"><code>N</code></em></code>.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.6.1.1.1" class="indexterm"></a>
<code class="function">regr_count</code> ( <em class="parameter"><code>Y</code></em> <code class="type">double precision</code>, <em class="parameter"><code>X</code></em> <code class="type">double precision</code> )
→ <code class="returnvalue">bigint</code>
</p>
<p>
Computes the number of rows in which both inputs are non-null.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.7.1.1.1" class="indexterm"></a>
<a id="id-1.5.8.27.13.2.4.7.1.1.2" class="indexterm"></a>
<code class="function">regr_intercept</code> ( <em class="parameter"><code>Y</code></em> <code class="type">double precision</code>, <em class="parameter"><code>X</code></em> <code class="type">double precision</code> )
→ <code class="returnvalue">double precision</code>
</p>
<p>
Computes the y-intercept of the least-squares-fit linear equation
determined by the
(<em class="parameter"><code>X</code></em>, <em class="parameter"><code>Y</code></em>) pairs.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.8.1.1.1" class="indexterm"></a>
<code class="function">regr_r2</code> ( <em class="parameter"><code>Y</code></em> <code class="type">double precision</code>, <em class="parameter"><code>X</code></em> <code class="type">double precision</code> )
→ <code class="returnvalue">double precision</code>
</p>
<p>
Computes the square of the correlation coefficient.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.9.1.1.1" class="indexterm"></a>
<a id="id-1.5.8.27.13.2.4.9.1.1.2" class="indexterm"></a>
<code class="function">regr_slope</code> ( <em class="parameter"><code>Y</code></em> <code class="type">double precision</code>, <em class="parameter"><code>X</code></em> <code class="type">double precision</code> )
→ <code class="returnvalue">double precision</code>
</p>
<p>
Computes the slope of the least-squares-fit linear equation determined
by the (<em class="parameter"><code>X</code></em>, <em class="parameter"><code>Y</code></em>)
pairs.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.10.1.1.1" class="indexterm"></a>
<code class="function">regr_sxx</code> ( <em class="parameter"><code>Y</code></em> <code class="type">double precision</code>, <em class="parameter"><code>X</code></em> <code class="type">double precision</code> )
→ <code class="returnvalue">double precision</code>
</p>
<p>
Computes the <span class="quote">“<span class="quote">sum of squares</span>”</span> of the independent
variable,
<code class="literal">sum(<em class="parameter"><code>X</code></em>^2) - sum(<em class="parameter"><code>X</code></em>)^2/<em class="parameter"><code>N</code></em></code>.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.11.1.1.1" class="indexterm"></a>
<code class="function">regr_sxy</code> ( <em class="parameter"><code>Y</code></em> <code class="type">double precision</code>, <em class="parameter"><code>X</code></em> <code class="type">double precision</code> )
→ <code class="returnvalue">double precision</code>
</p>
<p>
Computes the <span class="quote">“<span class="quote">sum of products</span>”</span> of independent times
dependent variables,
<code class="literal">sum(<em class="parameter"><code>X</code></em>*<em class="parameter"><code>Y</code></em>) - sum(<em class="parameter"><code>X</code></em>) * sum(<em class="parameter"><code>Y</code></em>)/<em class="parameter"><code>N</code></em></code>.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.12.1.1.1" class="indexterm"></a>
<code class="function">regr_syy</code> ( <em class="parameter"><code>Y</code></em> <code class="type">double precision</code>, <em class="parameter"><code>X</code></em> <code class="type">double precision</code> )
→ <code class="returnvalue">double precision</code>
</p>
<p>
Computes the <span class="quote">“<span class="quote">sum of squares</span>”</span> of the dependent
variable,
<code class="literal">sum(<em class="parameter"><code>Y</code></em>^2) - sum(<em class="parameter"><code>Y</code></em>)^2/<em class="parameter"><code>N</code></em></code>.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.13.1.1.1" class="indexterm"></a>
<a id="id-1.5.8.27.13.2.4.13.1.1.2" class="indexterm"></a>
<code class="function">stddev</code> ( <em class="replaceable"><code>numeric_type</code></em> )
→ <code class="returnvalue"></code> <code class="type">double precision</code>
for <code class="type">real</code> or <code class="type">double precision</code>,
otherwise <code class="type">numeric</code>
</p>
<p>
This is a historical alias for <code class="function">stddev_samp</code>.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.14.1.1.1" class="indexterm"></a>
<a id="id-1.5.8.27.13.2.4.14.1.1.2" class="indexterm"></a>
<code class="function">stddev_pop</code> ( <em class="replaceable"><code>numeric_type</code></em> )
→ <code class="returnvalue"></code> <code class="type">double precision</code>
for <code class="type">real</code> or <code class="type">double precision</code>,
otherwise <code class="type">numeric</code>
</p>
<p>
Computes the population standard deviation of the input values.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.15.1.1.1" class="indexterm"></a>
<a id="id-1.5.8.27.13.2.4.15.1.1.2" class="indexterm"></a>
<code class="function">stddev_samp</code> ( <em class="replaceable"><code>numeric_type</code></em> )
→ <code class="returnvalue"></code> <code class="type">double precision</code>
for <code class="type">real</code> or <code class="type">double precision</code>,
otherwise <code class="type">numeric</code>
</p>
<p>
Computes the sample standard deviation of the input values.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.16.1.1.1" class="indexterm"></a>
<code class="function">variance</code> ( <em class="replaceable"><code>numeric_type</code></em> )
→ <code class="returnvalue"></code> <code class="type">double precision</code>
for <code class="type">real</code> or <code class="type">double precision</code>,
otherwise <code class="type">numeric</code>
</p>
<p>
This is a historical alias for <code class="function">var_samp</code>.
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.17.1.1.1" class="indexterm"></a>
<a id="id-1.5.8.27.13.2.4.17.1.1.2" class="indexterm"></a>
<code class="function">var_pop</code> ( <em class="replaceable"><code>numeric_type</code></em> )
→ <code class="returnvalue"></code> <code class="type">double precision</code>
for <code class="type">real</code> or <code class="type">double precision</code>,
otherwise <code class="type">numeric</code>
</p>
<p>
Computes the population variance of the input values (square of the
population standard deviation).
</p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.13.2.4.18.1.1.1" class="indexterm"></a>
<a id="id-1.5.8.27.13.2.4.18.1.1.2" class="indexterm"></a>
<code class="function">var_samp</code> ( <em class="replaceable"><code>numeric_type</code></em> )
→ <code class="returnvalue"></code> <code class="type">double precision</code>
for <code class="type">real</code> or <code class="type">double precision</code>,
otherwise <code class="type">numeric</code>
</p>
<p>
Computes the sample variance of the input values (square of the sample
standard deviation).
</p></td><td>Yes</td></tr></tbody></table></div></div><br class="table-break" /><p>
<a class="xref" href="functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE" title="Table 9.61. Ordered-Set Aggregate Functions">Table 9.61</a> shows some
aggregate functions that use the <em class="firstterm">ordered-set aggregate</em>
syntax. These functions are sometimes referred to as <span class="quote">“<span class="quote">inverse
distribution</span>”</span> functions. Their aggregated input is introduced by
<code class="literal">ORDER BY</code>, and they may also take a <em class="firstterm">direct
argument</em> that is not aggregated, but is computed only once.
All these functions ignore null values in their aggregated input.
For those that take a <em class="parameter"><code>fraction</code></em> parameter, the
fraction value must be between 0 and 1; an error is thrown if not.
However, a null <em class="parameter"><code>fraction</code></em> value simply produces a
null result.
</p><a id="id-1.5.8.27.15" class="indexterm"></a><a id="id-1.5.8.27.16" class="indexterm"></a><div class="table" id="FUNCTIONS-ORDEREDSET-TABLE"><p class="title"><strong>Table 9.61. Ordered-Set Aggregate Functions</strong></p><div class="table-contents"><table class="table" summary="Ordered-Set Aggregate Functions" border="1"><colgroup><col class="col1" /><col class="col2" /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
Function
</p>
<p>
Description
</p></th><th>Partial Mode</th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.17.2.4.1.1.1.1" class="indexterm"></a>
<code class="function">mode</code> () <code class="literal">WITHIN GROUP</code> ( <code class="literal">ORDER BY</code> <code class="type">anyelement</code> )
→ <code class="returnvalue">anyelement</code>
</p>
<p>
Computes the <em class="firstterm">mode</em>, the most frequent
value of the aggregated argument (arbitrarily choosing the first one
if there are multiple equally-frequent values). The aggregated
argument must be of a sortable type.
</p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.17.2.4.2.1.1.1" class="indexterm"></a>
<code class="function">percentile_cont</code> ( <em class="parameter"><code>fraction</code></em> <code class="type">double precision</code> ) <code class="literal">WITHIN GROUP</code> ( <code class="literal">ORDER BY</code> <code class="type">double precision</code> )
→ <code class="returnvalue">double precision</code>
</p>
<p class="func_signature">
<code class="function">percentile_cont</code> ( <em class="parameter"><code>fraction</code></em> <code class="type">double precision</code> ) <code class="literal">WITHIN GROUP</code> ( <code class="literal">ORDER BY</code> <code class="type">interval</code> )
→ <code class="returnvalue">interval</code>
</p>
<p>
Computes the <em class="firstterm">continuous percentile</em>, a value
corresponding to the specified <em class="parameter"><code>fraction</code></em>
within the ordered set of aggregated argument values. This will
interpolate between adjacent input items if needed.
</p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">percentile_cont</code> ( <em class="parameter"><code>fractions</code></em> <code class="type">double precision[]</code> ) <code class="literal">WITHIN GROUP</code> ( <code class="literal">ORDER BY</code> <code class="type">double precision</code> )
→ <code class="returnvalue">double precision[]</code>
</p>
<p class="func_signature">
<code class="function">percentile_cont</code> ( <em class="parameter"><code>fractions</code></em> <code class="type">double precision[]</code> ) <code class="literal">WITHIN GROUP</code> ( <code class="literal">ORDER BY</code> <code class="type">interval</code> )
→ <code class="returnvalue">interval[]</code>
</p>
<p>
Computes multiple continuous percentiles. The result is an array of
the same dimensions as the <em class="parameter"><code>fractions</code></em>
parameter, with each non-null element replaced by the (possibly
interpolated) value corresponding to that percentile.
</p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.17.2.4.4.1.1.1" class="indexterm"></a>
<code class="function">percentile_disc</code> ( <em class="parameter"><code>fraction</code></em> <code class="type">double precision</code> ) <code class="literal">WITHIN GROUP</code> ( <code class="literal">ORDER BY</code> <code class="type">anyelement</code> )
→ <code class="returnvalue">anyelement</code>
</p>
<p>
Computes the <em class="firstterm">discrete percentile</em>, the first
value within the ordered set of aggregated argument values whose
position in the ordering equals or exceeds the
specified <em class="parameter"><code>fraction</code></em>. The aggregated
argument must be of a sortable type.
</p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">percentile_disc</code> ( <em class="parameter"><code>fractions</code></em> <code class="type">double precision[]</code> ) <code class="literal">WITHIN GROUP</code> ( <code class="literal">ORDER BY</code> <code class="type">anyelement</code> )
→ <code class="returnvalue">anyarray</code>
</p>
<p>
Computes multiple discrete percentiles. The result is an array of the
same dimensions as the <em class="parameter"><code>fractions</code></em> parameter,
with each non-null element replaced by the input value corresponding
to that percentile.
The aggregated argument must be of a sortable type.
</p></td><td>No</td></tr></tbody></table></div></div><br class="table-break" /><a id="id-1.5.8.27.18" class="indexterm"></a><p>
Each of the <span class="quote">“<span class="quote">hypothetical-set</span>”</span> aggregates listed in
<a class="xref" href="functions-aggregate.html#FUNCTIONS-HYPOTHETICAL-TABLE" title="Table 9.62. Hypothetical-Set Aggregate Functions">Table 9.62</a> is associated with a
window function of the same name defined in
<a class="xref" href="functions-window.html" title="9.22. Window Functions">Section 9.22</a>. In each case, the aggregate's result
is the value that the associated window function would have
returned for the <span class="quote">“<span class="quote">hypothetical</span>”</span> row constructed from
<em class="replaceable"><code>args</code></em>, if such a row had been added to the sorted
group of rows represented by the <em class="replaceable"><code>sorted_args</code></em>.
For each of these functions, the list of direct arguments
given in <em class="replaceable"><code>args</code></em> must match the number and types of
the aggregated arguments given in <em class="replaceable"><code>sorted_args</code></em>.
Unlike most built-in aggregates, these aggregates are not strict, that is
they do not drop input rows containing nulls. Null values sort according
to the rule specified in the <code class="literal">ORDER BY</code> clause.
</p><div class="table" id="FUNCTIONS-HYPOTHETICAL-TABLE"><p class="title"><strong>Table 9.62. Hypothetical-Set Aggregate Functions</strong></p><div class="table-contents"><table class="table" summary="Hypothetical-Set Aggregate Functions" border="1"><colgroup><col class="col1" /><col class="col2" /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
Function
</p>
<p>
Description
</p></th><th>Partial Mode</th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.20.2.4.1.1.1.1" class="indexterm"></a>
<code class="function">rank</code> ( <em class="replaceable"><code>args</code></em> ) <code class="literal">WITHIN GROUP</code> ( <code class="literal">ORDER BY</code> <em class="replaceable"><code>sorted_args</code></em> )
→ <code class="returnvalue">bigint</code>
</p>
<p>
Computes the rank of the hypothetical row, with gaps; that is, the row
number of the first row in its peer group.
</p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.20.2.4.2.1.1.1" class="indexterm"></a>
<code class="function">dense_rank</code> ( <em class="replaceable"><code>args</code></em> ) <code class="literal">WITHIN GROUP</code> ( <code class="literal">ORDER BY</code> <em class="replaceable"><code>sorted_args</code></em> )
→ <code class="returnvalue">bigint</code>
</p>
<p>
Computes the rank of the hypothetical row, without gaps; this function
effectively counts peer groups.
</p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.20.2.4.3.1.1.1" class="indexterm"></a>
<code class="function">percent_rank</code> ( <em class="replaceable"><code>args</code></em> ) <code class="literal">WITHIN GROUP</code> ( <code class="literal">ORDER BY</code> <em class="replaceable"><code>sorted_args</code></em> )
→ <code class="returnvalue">double precision</code>
</p>
<p>
Computes the relative rank of the hypothetical row, that is
(<code class="function">rank</code> - 1) / (total rows - 1).
The value thus ranges from 0 to 1 inclusive.
</p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.20.2.4.4.1.1.1" class="indexterm"></a>
<code class="function">cume_dist</code> ( <em class="replaceable"><code>args</code></em> ) <code class="literal">WITHIN GROUP</code> ( <code class="literal">ORDER BY</code> <em class="replaceable"><code>sorted_args</code></em> )
→ <code class="returnvalue">double precision</code>
</p>
<p>
Computes the cumulative distribution, that is (number of rows
preceding or peers with hypothetical row) / (total rows). The value
thus ranges from 1/<em class="parameter"><code>N</code></em> to 1.
</p></td><td>No</td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="FUNCTIONS-GROUPING-TABLE"><p class="title"><strong>Table 9.63. Grouping Operations</strong></p><div class="table-contents"><table class="table" summary="Grouping Operations" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
Function
</p>
<p>
Description
</p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.27.21.2.2.1.1.1.1" class="indexterm"></a>
<code class="function">GROUPING</code> ( <em class="replaceable"><code>group_by_expression(s)</code></em> )
→ <code class="returnvalue">integer</code>
</p>
<p>
Returns a bit mask indicating which <code class="literal">GROUP BY</code>
expressions are not included in the current grouping set.
Bits are assigned with the rightmost argument corresponding to the
least-significant bit; each bit is 0 if the corresponding expression
is included in the grouping criteria of the grouping set generating
the current result row, and 1 if it is not included.
</p></td></tr></tbody></table></div></div><br class="table-break" /><p>
The grouping operations shown in
<a class="xref" href="functions-aggregate.html#FUNCTIONS-GROUPING-TABLE" title="Table 9.63. Grouping Operations">Table 9.63</a> are used in conjunction with
grouping sets (see <a class="xref" href="queries-table-expressions.html#QUERIES-GROUPING-SETS" title="7.2.4. GROUPING SETS, CUBE, and ROLLUP">Section 7.2.4</a>) to distinguish
result rows. The arguments to the <code class="literal">GROUPING</code> function
are not actually evaluated, but they must exactly match expressions given
in the <code class="literal">GROUP BY</code> clause of the associated query level.
For example:
</p><pre class="screen">
<code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM items_sold;</code></strong>
make | model | sales
-------+-------+-------
Foo | GT | 10
Foo | Tour | 20
Bar | City | 15
Bar | Sport | 5
(4 rows)
<code class="prompt">=></code> <strong class="userinput"><code>SELECT make, model, GROUPING(make,model), sum(sales) FROM items_sold GROUP BY ROLLUP(make,model);</code></strong>
make | model | grouping | sum
-------+-------+----------+-----
Foo | GT | 0 | 10
Foo | Tour | 0 | 20
Bar | City | 0 | 15
Bar | Sport | 0 | 5
Foo | | 1 | 30
Bar | | 1 | 20
| | 3 | 50
(7 rows)
</pre><p>
Here, the <code class="literal">grouping</code> value <code class="literal">0</code> in the
first four rows shows that those have been grouped normally, over both the
grouping columns. The value <code class="literal">1</code> indicates
that <code class="literal">model</code> was not grouped by in the next-to-last two
rows, and the value <code class="literal">3</code> indicates that
neither <code class="literal">make</code> nor <code class="literal">model</code> was grouped
by in the last row (which therefore is an aggregate over all the input
rows).
</p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-range.html" title="9.20. Range/Multirange Functions and Operators">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-window.html" title="9.22. Window Functions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.20. Range/Multirange Functions and Operators </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.22. Window Functions</td></tr></table></div></body></html>
|
