Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
functions-aggregate.html853 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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>3   <em class="firstterm">Aggregate functions</em> compute a single result4   from a set of input values.  The built-in general-purpose aggregate5   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>6   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>.7   The built-in within-group ordered-set aggregate functions8   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>9   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,10   which are closely related to aggregate functions, are listed in11   <a class="xref" href="functions-aggregate.html#FUNCTIONS-GROUPING-TABLE" title="Table 9.63. Grouping Operations">Table 9.63</a>.12   The special syntax considerations for aggregate13   functions are explained in <a class="xref" href="sql-expressions.html#SYNTAX-AGGREGATES" title="4.2.7. Aggregate Expressions">Section 4.2.7</a>.14   Consult <a class="xref" href="tutorial-agg.html" title="2.7. Aggregate Functions">Section 2.7</a> for additional introductory15   information.16  </p><p>17   Aggregate functions that support <em class="firstterm">Partial Mode</em>18   are eligible to participate in various optimizations, such as parallel19   aggregation.20  </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">21        Function22       </p>23       <p>24        Description25       </p></th><th>Partial Mode</th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">26        <a id="id-1.5.8.27.5.2.4.1.1.1.1" class="indexterm"></a>27        <code class="function">any_value</code> ( <code class="type">anyelement</code> )28        → <code class="returnvalue"><em class="replaceable"><code>same as input type</code></em></code>29       </p>30       <p>31        Returns an arbitrary value from the non-null input values.32       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">33        <a id="id-1.5.8.27.5.2.4.2.1.1.1" class="indexterm"></a>34        <code class="function">array_agg</code> ( <code class="type">anynonarray</code> )35        → <code class="returnvalue">anyarray</code>36       </p>37       <p>38        Collects all the input values, including nulls, into an array.39       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">40        <code class="function">array_agg</code> ( <code class="type">anyarray</code> )41        → <code class="returnvalue">anyarray</code>42       </p>43       <p>44        Concatenates all the input arrays into an array of one higher45        dimension.  (The inputs must all have the same dimensionality, and46        cannot be empty or null.)47       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">48        <a id="id-1.5.8.27.5.2.4.4.1.1.1" class="indexterm"></a>49        <a id="id-1.5.8.27.5.2.4.4.1.1.2" class="indexterm"></a>50        <code class="function">avg</code> ( <code class="type">smallint</code> )51        → <code class="returnvalue">numeric</code>52       </p>53       <p class="func_signature">54        <code class="function">avg</code> ( <code class="type">integer</code> )55        → <code class="returnvalue">numeric</code>56       </p>57       <p class="func_signature">58        <code class="function">avg</code> ( <code class="type">bigint</code> )59        → <code class="returnvalue">numeric</code>60       </p>61       <p class="func_signature">62        <code class="function">avg</code> ( <code class="type">numeric</code> )63        → <code class="returnvalue">numeric</code>64       </p>65       <p class="func_signature">66        <code class="function">avg</code> ( <code class="type">real</code> )67        → <code class="returnvalue">double precision</code>68       </p>69       <p class="func_signature">70        <code class="function">avg</code> ( <code class="type">double precision</code> )71        → <code class="returnvalue">double precision</code>72       </p>73       <p class="func_signature">74        <code class="function">avg</code> ( <code class="type">interval</code> )75        → <code class="returnvalue">interval</code>76       </p>77       <p>78        Computes the average (arithmetic mean) of all the non-null input79        values.80       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">81        <a id="id-1.5.8.27.5.2.4.5.1.1.1" class="indexterm"></a>82        <code class="function">bit_and</code> ( <code class="type">smallint</code> )83        → <code class="returnvalue">smallint</code>84       </p>85       <p class="func_signature">86        <code class="function">bit_and</code> ( <code class="type">integer</code> )87        → <code class="returnvalue">integer</code>88       </p>89       <p class="func_signature">90        <code class="function">bit_and</code> ( <code class="type">bigint</code> )91        → <code class="returnvalue">bigint</code>92       </p>93       <p class="func_signature">94        <code class="function">bit_and</code> ( <code class="type">bit</code> )95        → <code class="returnvalue">bit</code>96       </p>97       <p>98        Computes the bitwise AND of all non-null input values.99       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">100        <a id="id-1.5.8.27.5.2.4.6.1.1.1" class="indexterm"></a>101        <code class="function">bit_or</code> ( <code class="type">smallint</code> )102        → <code class="returnvalue">smallint</code>103       </p>104       <p class="func_signature">105        <code class="function">bit_or</code> ( <code class="type">integer</code> )106        → <code class="returnvalue">integer</code>107       </p>108       <p class="func_signature">109        <code class="function">bit_or</code> ( <code class="type">bigint</code> )110        → <code class="returnvalue">bigint</code>111       </p>112       <p class="func_signature">113        <code class="function">bit_or</code> ( <code class="type">bit</code> )114        → <code class="returnvalue">bit</code>115       </p>116       <p>117        Computes the bitwise OR of all non-null input values.118       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">119        <a id="id-1.5.8.27.5.2.4.7.1.1.1" class="indexterm"></a>120        <code class="function">bit_xor</code> ( <code class="type">smallint</code> )121        → <code class="returnvalue">smallint</code>122       </p>123       <p class="func_signature">124        <code class="function">bit_xor</code> ( <code class="type">integer</code> )125        → <code class="returnvalue">integer</code>126       </p>127       <p class="func_signature">128        <code class="function">bit_xor</code> ( <code class="type">bigint</code> )129        → <code class="returnvalue">bigint</code>130       </p>131       <p class="func_signature">132        <code class="function">bit_xor</code> ( <code class="type">bit</code> )133        → <code class="returnvalue">bit</code>134       </p>135       <p>136        Computes the bitwise exclusive OR of all non-null input values.137        Can be useful as a checksum for an unordered set of values.138       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">139        <a id="id-1.5.8.27.5.2.4.8.1.1.1" class="indexterm"></a>140        <code class="function">bool_and</code> ( <code class="type">boolean</code> )141        → <code class="returnvalue">boolean</code>142       </p>143       <p>144        Returns true if all non-null input values are true, otherwise false.145       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">146        <a id="id-1.5.8.27.5.2.4.9.1.1.1" class="indexterm"></a>147        <code class="function">bool_or</code> ( <code class="type">boolean</code> )148        → <code class="returnvalue">boolean</code>149       </p>150       <p>151        Returns true if any non-null input value is true, otherwise false.152       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">153        <a id="id-1.5.8.27.5.2.4.10.1.1.1" class="indexterm"></a>154        <code class="function">count</code> ( <code class="literal">*</code> )155        → <code class="returnvalue">bigint</code>156       </p>157       <p>158        Computes the number of input rows.159       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">160        <code class="function">count</code> ( <code class="type">"any"</code> )161        → <code class="returnvalue">bigint</code>162       </p>163       <p>164        Computes the number of input rows in which the input value is not165        null.166       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">167        <a id="id-1.5.8.27.5.2.4.12.1.1.1" class="indexterm"></a>168        <code class="function">every</code> ( <code class="type">boolean</code> )169        → <code class="returnvalue">boolean</code>170       </p>171       <p>172        This is the SQL standard's equivalent to <code class="function">bool_and</code>.173       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">174        <a id="id-1.5.8.27.5.2.4.13.1.1.1" class="indexterm"></a>175        <code class="function">json_agg</code> ( <code class="type">anyelement</code> )176        → <code class="returnvalue">json</code>177       </p>178       <p class="func_signature">179        <a id="id-1.5.8.27.5.2.4.13.1.2.1" class="indexterm"></a>180        <code class="function">jsonb_agg</code> ( <code class="type">anyelement</code> )181        → <code class="returnvalue">jsonb</code>182       </p>183       <p>184        Collects all the input values, including nulls, into a JSON array.185        Values are converted to JSON as per <code class="function">to_json</code>186        or <code class="function">to_jsonb</code>.187       </p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">188         <a id="id-1.5.8.27.5.2.4.14.1.1.1" class="indexterm"></a>189         <code class="function">json_objectagg</code> (190         [<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>]191         [<span class="optional"> { <code class="literal">NULL</code> | <code class="literal">ABSENT</code> } <code class="literal">ON NULL</code> </span>]192        [<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>]193        [<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>])194        </p>195        <p>196         Behaves like <code class="function">json_object</code>, but as an197         aggregate function, so it only takes one198         <em class="replaceable"><code>key_expression</code></em> and one199         <em class="replaceable"><code>value_expression</code></em> parameter.200        </p>201        <p>202         <code class="literal">SELECT json_objectagg(k:v) FROM (VALUES ('a'::text,current_date),('b',current_date + 1)) AS t(k,v)</code>203         → <code class="returnvalue">{ "a" : "2022-05-10", "b" : "2022-05-11" }</code>204       </p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">205        <a id="id-1.5.8.27.5.2.4.15.1.1.1" class="indexterm"></a>206        <code class="function">json_object_agg</code> ( <em class="parameter"><code>key</code></em>207         <code class="type">"any"</code>, <em class="parameter"><code>value</code></em>208         <code class="type">"any"</code> )209        → <code class="returnvalue">json</code>210       </p>211       <p class="func_signature">212        <a id="id-1.5.8.27.5.2.4.15.1.2.1" class="indexterm"></a>213        <code class="function">jsonb_object_agg</code> ( <em class="parameter"><code>key</code></em>214         <code class="type">"any"</code>, <em class="parameter"><code>value</code></em>215         <code class="type">"any"</code> )216        → <code class="returnvalue">jsonb</code>217       </p>218       <p>219        Collects all the key/value pairs into a JSON object.  Key arguments220        are coerced to text; value arguments are converted as per221        <code class="function">to_json</code> or <code class="function">to_jsonb</code>.222        Values can be null, but keys cannot.223       </p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">224        <a id="id-1.5.8.27.5.2.4.16.1.1.1" class="indexterm"></a>225        <code class="function">json_object_agg_strict</code> (226         <em class="parameter"><code>key</code></em> <code class="type">"any"</code>,227         <em class="parameter"><code>value</code></em> <code class="type">"any"</code> )228        → <code class="returnvalue">json</code>229       </p>230       <p class="func_signature">231        <a id="id-1.5.8.27.5.2.4.16.1.2.1" class="indexterm"></a>232        <code class="function">jsonb_object_agg_strict</code> (233         <em class="parameter"><code>key</code></em> <code class="type">"any"</code>,234         <em class="parameter"><code>value</code></em> <code class="type">"any"</code> )235        → <code class="returnvalue">jsonb</code>236       </p>237       <p>238        Collects all the key/value pairs into a JSON object.  Key arguments239        are coerced to text; value arguments are converted as per240        <code class="function">to_json</code> or <code class="function">to_jsonb</code>.241        The <em class="parameter"><code>key</code></em> can not be null. If the242        <em class="parameter"><code>value</code></em> is null then the entry is skipped,243       </p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">244        <a id="id-1.5.8.27.5.2.4.17.1.1.1" class="indexterm"></a>245        <code class="function">json_object_agg_unique</code> (246         <em class="parameter"><code>key</code></em> <code class="type">"any"</code>,247         <em class="parameter"><code>value</code></em> <code class="type">"any"</code> )248        → <code class="returnvalue">json</code>249       </p>250       <p class="func_signature">251        <a id="id-1.5.8.27.5.2.4.17.1.2.1" class="indexterm"></a>252        <code class="function">jsonb_object_agg_unique</code> (253         <em class="parameter"><code>key</code></em> <code class="type">"any"</code>,254         <em class="parameter"><code>value</code></em> <code class="type">"any"</code> )255        → <code class="returnvalue">jsonb</code>256       </p>257       <p>258        Collects all the key/value pairs into a JSON object.  Key arguments259        are coerced to text; value arguments are converted as per260        <code class="function">to_json</code> or <code class="function">to_jsonb</code>.261        Values can be null, but keys cannot.262        If there is a duplicate key an error is thrown.263       </p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">264        <a id="id-1.5.8.27.5.2.4.18.1.1.1" class="indexterm"></a>265        <code class="function">json_arrayagg</code> (266        [<span class="optional"> <em class="replaceable"><code>value_expression</code></em> </span>]267        [<span class="optional"> <code class="literal">ORDER BY</code> <em class="replaceable"><code>sort_expression</code></em> </span>]268        [<span class="optional"> { <code class="literal">NULL</code> | <code class="literal">ABSENT</code> } <code class="literal">ON NULL</code> </span>]269        [<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>])270       </p>271       <p>272        Behaves in the same way as <code class="function">json_array</code>273        but as an aggregate function so it only takes one274        <em class="replaceable"><code>value_expression</code></em> parameter.275        If <code class="literal">ABSENT ON NULL</code> is specified, any NULL276        values are omitted.277        If <code class="literal">ORDER BY</code> is specified, the elements will278        appear in the array in that order rather than in the input order.279       </p>280       <p>281        <code class="literal">SELECT json_arrayagg(v) FROM (VALUES(2),(1)) t(v)</code>282        → <code class="returnvalue">[2, 1]</code>283       </p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">284        <a id="id-1.5.8.27.5.2.4.19.1.1.1" class="indexterm"></a>285        <code class="function">json_object_agg_unique_strict</code> (286         <em class="parameter"><code>key</code></em> <code class="type">"any"</code>,287         <em class="parameter"><code>value</code></em> <code class="type">"any"</code> )288        → <code class="returnvalue">json</code>289       </p>290       <p class="func_signature">291        <a id="id-1.5.8.27.5.2.4.19.1.2.1" class="indexterm"></a>292        <code class="function">jsonb_object_agg_unique_strict</code> (293         <em class="parameter"><code>key</code></em> <code class="type">"any"</code>,294         <em class="parameter"><code>value</code></em> <code class="type">"any"</code> )295        → <code class="returnvalue">jsonb</code>296       </p>297       <p>298        Collects all the key/value pairs into a JSON object.  Key arguments299        are coerced to text; value arguments are converted as per300        <code class="function">to_json</code> or <code class="function">to_jsonb</code>.301        The <em class="parameter"><code>key</code></em> can not be null. If the302        <em class="parameter"><code>value</code></em> is null then the entry is skipped.303        If there is a duplicate key an error is thrown.304       </p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">305        <a id="id-1.5.8.27.5.2.4.20.1.1.1" class="indexterm"></a>306        <code class="function">max</code> ( <em class="replaceable"><code>see text</code></em> )307        → <code class="returnvalue"><em class="replaceable"><code>same as input type</code></em></code>308       </p>309       <p>310        Computes the maximum of the non-null input311        values.  Available for any numeric, string, date/time, or enum type,312        as well as <code class="type">inet</code>, <code class="type">interval</code>,313        <code class="type">money</code>, <code class="type">oid</code>, <code class="type">pg_lsn</code>,314        <code class="type">tid</code>, <code class="type">xid8</code>,315        and arrays of any of these types.316       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">317        <a id="id-1.5.8.27.5.2.4.21.1.1.1" class="indexterm"></a>318        <code class="function">min</code> ( <em class="replaceable"><code>see text</code></em> )319        → <code class="returnvalue"><em class="replaceable"><code>same as input type</code></em></code>320       </p>321       <p>322        Computes the minimum of the non-null input323        values.  Available for any numeric, string, date/time, or enum type,324        as well as <code class="type">inet</code>, <code class="type">interval</code>,325        <code class="type">money</code>, <code class="type">oid</code>, <code class="type">pg_lsn</code>,326        <code class="type">tid</code>, <code class="type">xid8</code>,327        and arrays of any of these types.328       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">329        <a id="id-1.5.8.27.5.2.4.22.1.1.1" class="indexterm"></a>330        <code class="function">range_agg</code> ( <em class="parameter"><code>value</code></em>331         <code class="type">anyrange</code> )332        → <code class="returnvalue">anymultirange</code>333       </p>334       <p class="func_signature">335        <code class="function">range_agg</code> ( <em class="parameter"><code>value</code></em>336         <code class="type">anymultirange</code> )337        → <code class="returnvalue">anymultirange</code>338       </p>339       <p>340        Computes the union of the non-null input values.341       </p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">342        <a id="id-1.5.8.27.5.2.4.23.1.1.1" class="indexterm"></a>343        <code class="function">range_intersect_agg</code> ( <em class="parameter"><code>value</code></em>344         <code class="type">anyrange</code> )345        → <code class="returnvalue">anyrange</code>346       </p>347       <p class="func_signature">348        <code class="function">range_intersect_agg</code> ( <em class="parameter"><code>value</code></em>349         <code class="type">anymultirange</code> )350        → <code class="returnvalue">anymultirange</code>351       </p>352       <p>353        Computes the intersection of the non-null input values.354       </p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">355        <a id="id-1.5.8.27.5.2.4.24.1.1.1" class="indexterm"></a>356        <code class="function">json_agg_strict</code> ( <code class="type">anyelement</code> )357        → <code class="returnvalue">json</code>358       </p>359       <p class="func_signature">360        <a id="id-1.5.8.27.5.2.4.24.1.2.1" class="indexterm"></a>361        <code class="function">jsonb_agg_strict</code> ( <code class="type">anyelement</code> )362        → <code class="returnvalue">jsonb</code>363       </p>364       <p>365        Collects all the input values, skipping nulls, into a JSON array.366        Values are converted to JSON as per <code class="function">to_json</code>367        or <code class="function">to_jsonb</code>.368       </p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">369        <a id="id-1.5.8.27.5.2.4.25.1.1.1" class="indexterm"></a>370        <code class="function">string_agg</code> ( <em class="parameter"><code>value</code></em>371         <code class="type">text</code>, <em class="parameter"><code>delimiter</code></em> <code class="type">text</code> )372        → <code class="returnvalue">text</code>373       </p>374       <p class="func_signature">375        <code class="function">string_agg</code> ( <em class="parameter"><code>value</code></em>376         <code class="type">bytea</code>, <em class="parameter"><code>delimiter</code></em> <code class="type">bytea</code> )377        → <code class="returnvalue">bytea</code>378       </p>379       <p>380        Concatenates the non-null input values into a string.  Each value381        after the first is preceded by the382        corresponding <em class="parameter"><code>delimiter</code></em> (if it's not null).383       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">384        <a id="id-1.5.8.27.5.2.4.26.1.1.1" class="indexterm"></a>385        <code class="function">sum</code> ( <code class="type">smallint</code> )386        → <code class="returnvalue">bigint</code>387       </p>388       <p class="func_signature">389        <code class="function">sum</code> ( <code class="type">integer</code> )390        → <code class="returnvalue">bigint</code>391       </p>392       <p class="func_signature">393        <code class="function">sum</code> ( <code class="type">bigint</code> )394        → <code class="returnvalue">numeric</code>395       </p>396       <p class="func_signature">397        <code class="function">sum</code> ( <code class="type">numeric</code> )398        → <code class="returnvalue">numeric</code>399       </p>400       <p class="func_signature">401        <code class="function">sum</code> ( <code class="type">real</code> )402        → <code class="returnvalue">real</code>403       </p>404       <p class="func_signature">405        <code class="function">sum</code> ( <code class="type">double precision</code> )406        → <code class="returnvalue">double precision</code>407       </p>408       <p class="func_signature">409        <code class="function">sum</code> ( <code class="type">interval</code> )410        → <code class="returnvalue">interval</code>411       </p>412       <p class="func_signature">413        <code class="function">sum</code> ( <code class="type">money</code> )414        → <code class="returnvalue">money</code>415       </p>416       <p>417        Computes the sum of the non-null input values.418       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">419        <a id="id-1.5.8.27.5.2.4.27.1.1.1" class="indexterm"></a>420        <code class="function">xmlagg</code> ( <code class="type">xml</code> )421        → <code class="returnvalue">xml</code>422       </p>423       <p>424        Concatenates the non-null XML input values (see425        <a class="xref" href="functions-xml.html#FUNCTIONS-XML-XMLAGG" title="9.15.1.7. xmlagg">Section 9.15.1.7</a>).426       </p></td><td>No</td></tr></tbody></table></div></div><br class="table-break" /><p>427   It should be noted that except for <code class="function">count</code>,428   these functions return a null value when no rows are selected.  In429   particular, <code class="function">sum</code> of no rows returns null, not430   zero as one might expect, and <code class="function">array_agg</code>431   returns null rather than an empty array when there are no input432   rows.  The <code class="function">coalesce</code> function can be used to433   substitute zero or an empty array for null when necessary.434  </p><p>435   The aggregate functions <code class="function">array_agg</code>,436   <code class="function">json_agg</code>, <code class="function">jsonb_agg</code>,437   <code class="function">json_agg_strict</code>, <code class="function">jsonb_agg_strict</code>,438   <code class="function">json_object_agg</code>, <code class="function">jsonb_object_agg</code>,439   <code class="function">json_object_agg_strict</code>, <code class="function">jsonb_object_agg_strict</code>,440   <code class="function">json_object_agg_unique</code>, <code class="function">jsonb_object_agg_unique</code>,441   <code class="function">json_object_agg_unique_strict</code>,442   <code class="function">jsonb_object_agg_unique_strict</code>,443   <code class="function">string_agg</code>,444   and <code class="function">xmlagg</code>, as well as similar user-defined445   aggregate functions, produce meaningfully different result values446   depending on the order of the input values.  This ordering is447   unspecified by default, but can be controlled by writing an448   <code class="literal">ORDER BY</code> clause within the aggregate call, as shown in449   <a class="xref" href="sql-expressions.html#SYNTAX-AGGREGATES" title="4.2.7. Aggregate Expressions">Section 4.2.7</a>.450   Alternatively, supplying the input values from a sorted subquery451   will usually work.  For example:452 453</p><pre class="screen">454SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;455</pre><p>456 457   Beware that this approach can fail if the outer query level contains458   additional processing, such as a join, because that might cause the459   subquery's output to be reordered before the aggregate is computed.460  </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>461      The boolean aggregates <code class="function">bool_and</code> and462      <code class="function">bool_or</code> correspond to the standard SQL aggregates463      <code class="function">every</code> and <code class="function">any</code> or464      <code class="function">some</code>.465      <span class="productname">PostgreSQL</span>466      supports <code class="function">every</code>, but not <code class="function">any</code>467      or <code class="function">some</code>, because there is an ambiguity built into468      the standard syntax:469</p><pre class="programlisting">470SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;471</pre><p>472      Here <code class="function">ANY</code> can be considered either as introducing473      a subquery, or as being an aggregate function, if the subquery474      returns one row with a Boolean value.475      Thus the standard name cannot be given to these aggregates.476    </p></div><div class="note"><h3 class="title">Note</h3><p>477    Users accustomed to working with other SQL database management478    systems might be disappointed by the performance of the479    <code class="function">count</code> aggregate when it is applied to the480    entire table. A query like:481</p><pre class="programlisting">482SELECT count(*) FROM sometable;483</pre><p>484    will require effort proportional to the size of the table:485    <span class="productname">PostgreSQL</span> will need to scan either the486    entire table or the entirety of an index that includes all rows in487    the table.488   </p></div><p>489   <a class="xref" href="functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE" title="Table 9.60. Aggregate Functions for Statistics">Table 9.60</a> shows490   aggregate functions typically used in statistical analysis.491   (These are separated out merely to avoid cluttering the listing492   of more-commonly-used aggregates.)  Functions shown as493   accepting <em class="replaceable"><code>numeric_type</code></em> are available for all494   the types <code class="type">smallint</code>, <code class="type">integer</code>,495   <code class="type">bigint</code>, <code class="type">numeric</code>, <code class="type">real</code>,496   and <code class="type">double precision</code>.497   Where the description mentions498   <em class="parameter"><code>N</code></em>, it means the499   number of input rows for which all the input expressions are non-null.500   In all cases, null is returned if the computation is meaningless,501   for example when <em class="parameter"><code>N</code></em> is zero.502  </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">503        Function504       </p>505       <p>506        Description507       </p></th><th>Partial Mode</th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">508        <a id="id-1.5.8.27.13.2.4.1.1.1.1" class="indexterm"></a>509        <a id="id-1.5.8.27.13.2.4.1.1.1.2" class="indexterm"></a>510        <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> )511        → <code class="returnvalue">double precision</code>512       </p>513       <p>514        Computes the correlation coefficient.515       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">516        <a id="id-1.5.8.27.13.2.4.2.1.1.1" class="indexterm"></a>517        <a id="id-1.5.8.27.13.2.4.2.1.1.2" class="indexterm"></a>518        <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> )519        → <code class="returnvalue">double precision</code>520       </p>521       <p>522        Computes the population covariance.523       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">524        <a id="id-1.5.8.27.13.2.4.3.1.1.1" class="indexterm"></a>525        <a id="id-1.5.8.27.13.2.4.3.1.1.2" class="indexterm"></a>526        <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> )527        → <code class="returnvalue">double precision</code>528       </p>529       <p>530        Computes the sample covariance.531       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">532        <a id="id-1.5.8.27.13.2.4.4.1.1.1" class="indexterm"></a>533        <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> )534        → <code class="returnvalue">double precision</code>535       </p>536       <p>537        Computes the average of the independent variable,538        <code class="literal">sum(<em class="parameter"><code>X</code></em>)/<em class="parameter"><code>N</code></em></code>.539       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">540        <a id="id-1.5.8.27.13.2.4.5.1.1.1" class="indexterm"></a>541        <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> )542        → <code class="returnvalue">double precision</code>543       </p>544       <p>545        Computes the average of the dependent variable,546        <code class="literal">sum(<em class="parameter"><code>Y</code></em>)/<em class="parameter"><code>N</code></em></code>.547       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">548        <a id="id-1.5.8.27.13.2.4.6.1.1.1" class="indexterm"></a>549        <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> )550        → <code class="returnvalue">bigint</code>551       </p>552       <p>553        Computes the number of rows in which both inputs are non-null.554       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">555        <a id="id-1.5.8.27.13.2.4.7.1.1.1" class="indexterm"></a>556        <a id="id-1.5.8.27.13.2.4.7.1.1.2" class="indexterm"></a>557        <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> )558        → <code class="returnvalue">double precision</code>559       </p>560       <p>561        Computes the y-intercept of the least-squares-fit linear equation562        determined by the563        (<em class="parameter"><code>X</code></em>, <em class="parameter"><code>Y</code></em>) pairs.564       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">565        <a id="id-1.5.8.27.13.2.4.8.1.1.1" class="indexterm"></a>566        <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> )567        → <code class="returnvalue">double precision</code>568       </p>569       <p>570        Computes the square of the correlation coefficient.571       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">572        <a id="id-1.5.8.27.13.2.4.9.1.1.1" class="indexterm"></a>573        <a id="id-1.5.8.27.13.2.4.9.1.1.2" class="indexterm"></a>574        <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> )575        → <code class="returnvalue">double precision</code>576       </p>577       <p>578        Computes the slope of the least-squares-fit linear equation determined579        by the (<em class="parameter"><code>X</code></em>, <em class="parameter"><code>Y</code></em>)580        pairs.581       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">582        <a id="id-1.5.8.27.13.2.4.10.1.1.1" class="indexterm"></a>583        <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> )584        → <code class="returnvalue">double precision</code>585       </p>586       <p>587        Computes the <span class="quote">“<span class="quote">sum of squares</span>”</span> of the independent588        variable,589        <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>.590       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">591        <a id="id-1.5.8.27.13.2.4.11.1.1.1" class="indexterm"></a>592        <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> )593        → <code class="returnvalue">double precision</code>594       </p>595       <p>596        Computes the <span class="quote">“<span class="quote">sum of products</span>”</span> of independent times597        dependent variables,598        <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>.599       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">600        <a id="id-1.5.8.27.13.2.4.12.1.1.1" class="indexterm"></a>601        <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> )602        → <code class="returnvalue">double precision</code>603       </p>604       <p>605        Computes the <span class="quote">“<span class="quote">sum of squares</span>”</span> of the dependent606        variable,607        <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>.608       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">609        <a id="id-1.5.8.27.13.2.4.13.1.1.1" class="indexterm"></a>610        <a id="id-1.5.8.27.13.2.4.13.1.1.2" class="indexterm"></a>611        <code class="function">stddev</code> ( <em class="replaceable"><code>numeric_type</code></em> )612        → <code class="returnvalue"></code> <code class="type">double precision</code>613        for <code class="type">real</code> or <code class="type">double precision</code>,614        otherwise <code class="type">numeric</code>615       </p>616       <p>617        This is a historical alias for <code class="function">stddev_samp</code>.618       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">619        <a id="id-1.5.8.27.13.2.4.14.1.1.1" class="indexterm"></a>620        <a id="id-1.5.8.27.13.2.4.14.1.1.2" class="indexterm"></a>621        <code class="function">stddev_pop</code> ( <em class="replaceable"><code>numeric_type</code></em> )622        → <code class="returnvalue"></code> <code class="type">double precision</code>623        for <code class="type">real</code> or <code class="type">double precision</code>,624        otherwise <code class="type">numeric</code>625       </p>626       <p>627        Computes the population standard deviation of the input values.628       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">629        <a id="id-1.5.8.27.13.2.4.15.1.1.1" class="indexterm"></a>630        <a id="id-1.5.8.27.13.2.4.15.1.1.2" class="indexterm"></a>631        <code class="function">stddev_samp</code> ( <em class="replaceable"><code>numeric_type</code></em> )632        → <code class="returnvalue"></code> <code class="type">double precision</code>633        for <code class="type">real</code> or <code class="type">double precision</code>,634        otherwise <code class="type">numeric</code>635       </p>636       <p>637        Computes the sample standard deviation of the input values.638       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">639        <a id="id-1.5.8.27.13.2.4.16.1.1.1" class="indexterm"></a>640        <code class="function">variance</code> ( <em class="replaceable"><code>numeric_type</code></em> )641        → <code class="returnvalue"></code> <code class="type">double precision</code>642        for <code class="type">real</code> or <code class="type">double precision</code>,643        otherwise <code class="type">numeric</code>644       </p>645       <p>646        This is a historical alias for <code class="function">var_samp</code>.647       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">648        <a id="id-1.5.8.27.13.2.4.17.1.1.1" class="indexterm"></a>649        <a id="id-1.5.8.27.13.2.4.17.1.1.2" class="indexterm"></a>650        <code class="function">var_pop</code> ( <em class="replaceable"><code>numeric_type</code></em> )651        → <code class="returnvalue"></code> <code class="type">double precision</code>652        for <code class="type">real</code> or <code class="type">double precision</code>,653        otherwise <code class="type">numeric</code>654       </p>655       <p>656        Computes the population variance of the input values (square of the657        population standard deviation).658       </p></td><td>Yes</td></tr><tr><td class="func_table_entry"><p class="func_signature">659        <a id="id-1.5.8.27.13.2.4.18.1.1.1" class="indexterm"></a>660        <a id="id-1.5.8.27.13.2.4.18.1.1.2" class="indexterm"></a>661        <code class="function">var_samp</code> ( <em class="replaceable"><code>numeric_type</code></em> )662        → <code class="returnvalue"></code> <code class="type">double precision</code>663        for <code class="type">real</code> or <code class="type">double precision</code>,664        otherwise <code class="type">numeric</code>665       </p>666       <p>667        Computes the sample variance of the input values (square of the sample668        standard deviation).669       </p></td><td>Yes</td></tr></tbody></table></div></div><br class="table-break" /><p>670   <a class="xref" href="functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE" title="Table 9.61. Ordered-Set Aggregate Functions">Table 9.61</a> shows some671   aggregate functions that use the <em class="firstterm">ordered-set aggregate</em>672   syntax.  These functions are sometimes referred to as <span class="quote">“<span class="quote">inverse673   distribution</span>”</span> functions.  Their aggregated input is introduced by674   <code class="literal">ORDER BY</code>, and they may also take a <em class="firstterm">direct675   argument</em> that is not aggregated, but is computed only once.676   All these functions ignore null values in their aggregated input.677   For those that take a <em class="parameter"><code>fraction</code></em> parameter, the678   fraction value must be between 0 and 1; an error is thrown if not.679   However, a null <em class="parameter"><code>fraction</code></em> value simply produces a680   null result.681  </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">682        Function683       </p>684       <p>685        Description686       </p></th><th>Partial Mode</th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">687        <a id="id-1.5.8.27.17.2.4.1.1.1.1" class="indexterm"></a>688        <code class="function">mode</code> () <code class="literal">WITHIN GROUP</code> ( <code class="literal">ORDER BY</code> <code class="type">anyelement</code> )689        → <code class="returnvalue">anyelement</code>690       </p>691       <p>692        Computes the <em class="firstterm">mode</em>, the most frequent693        value of the aggregated argument (arbitrarily choosing the first one694        if there are multiple equally-frequent values).  The aggregated695        argument must be of a sortable type.696       </p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">697        <a id="id-1.5.8.27.17.2.4.2.1.1.1" class="indexterm"></a>698        <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> )699        → <code class="returnvalue">double precision</code>700       </p>701       <p class="func_signature">702        <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> )703        → <code class="returnvalue">interval</code>704       </p>705       <p>706        Computes the <em class="firstterm">continuous percentile</em>, a value707        corresponding to the specified <em class="parameter"><code>fraction</code></em>708        within the ordered set of aggregated argument values.  This will709        interpolate between adjacent input items if needed.710       </p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">711        <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> )712        → <code class="returnvalue">double precision[]</code>713       </p>714       <p class="func_signature">715        <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> )716        → <code class="returnvalue">interval[]</code>717       </p>718       <p>719        Computes multiple continuous percentiles.  The result is an array of720        the same dimensions as the <em class="parameter"><code>fractions</code></em>721        parameter, with each non-null element replaced by the (possibly722        interpolated) value corresponding to that percentile.723       </p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">724        <a id="id-1.5.8.27.17.2.4.4.1.1.1" class="indexterm"></a>725        <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> )726        → <code class="returnvalue">anyelement</code>727       </p>728       <p>729        Computes the <em class="firstterm">discrete percentile</em>, the first730        value within the ordered set of aggregated argument values whose731        position in the ordering equals or exceeds the732        specified <em class="parameter"><code>fraction</code></em>.  The aggregated733        argument must be of a sortable type.734       </p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">735        <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> )736        → <code class="returnvalue">anyarray</code>737       </p>738       <p>739        Computes multiple discrete percentiles.  The result is an array of the740        same dimensions as the <em class="parameter"><code>fractions</code></em> parameter,741        with each non-null element replaced by the input value corresponding742        to that percentile.743        The aggregated argument must be of a sortable type.744       </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>745   Each of the <span class="quote">“<span class="quote">hypothetical-set</span>”</span> aggregates listed in746   <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 a747   window function of the same name defined in748   <a class="xref" href="functions-window.html" title="9.22. Window Functions">Section 9.22</a>.  In each case, the aggregate's result749   is the value that the associated window function would have750   returned for the <span class="quote">“<span class="quote">hypothetical</span>”</span> row constructed from751   <em class="replaceable"><code>args</code></em>, if such a row had been added to the sorted752   group of rows represented by the <em class="replaceable"><code>sorted_args</code></em>.753   For each of these functions, the list of direct arguments754   given in <em class="replaceable"><code>args</code></em> must match the number and types of755   the aggregated arguments given in <em class="replaceable"><code>sorted_args</code></em>.756   Unlike most built-in aggregates, these aggregates are not strict, that is757   they do not drop input rows containing nulls.  Null values sort according758   to the rule specified in the <code class="literal">ORDER BY</code> clause.759  </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">760        Function761       </p>762       <p>763        Description764       </p></th><th>Partial Mode</th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">765        <a id="id-1.5.8.27.20.2.4.1.1.1.1" class="indexterm"></a>766        <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> )767        → <code class="returnvalue">bigint</code>768       </p>769       <p>770        Computes the rank of the hypothetical row, with gaps; that is, the row771        number of the first row in its peer group.772       </p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">773        <a id="id-1.5.8.27.20.2.4.2.1.1.1" class="indexterm"></a>774        <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> )775        → <code class="returnvalue">bigint</code>776       </p>777       <p>778        Computes the rank of the hypothetical row, without gaps; this function779        effectively counts peer groups.780       </p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">781        <a id="id-1.5.8.27.20.2.4.3.1.1.1" class="indexterm"></a>782        <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> )783        → <code class="returnvalue">double precision</code>784       </p>785       <p>786        Computes the relative rank of the hypothetical row, that is787        (<code class="function">rank</code> - 1) / (total rows - 1).788        The value thus ranges from 0 to 1 inclusive.789       </p></td><td>No</td></tr><tr><td class="func_table_entry"><p class="func_signature">790        <a id="id-1.5.8.27.20.2.4.4.1.1.1" class="indexterm"></a>791        <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> )792        → <code class="returnvalue">double precision</code>793       </p>794       <p>795        Computes the cumulative distribution, that is (number of rows796        preceding or peers with hypothetical row) / (total rows).  The value797        thus ranges from 1/<em class="parameter"><code>N</code></em> to 1.798       </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">799        Function800       </p>801       <p>802        Description803       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">804        <a id="id-1.5.8.27.21.2.2.1.1.1.1" class="indexterm"></a>805        <code class="function">GROUPING</code> ( <em class="replaceable"><code>group_by_expression(s)</code></em> )806        → <code class="returnvalue">integer</code>807       </p>808       <p>809        Returns a bit mask indicating which <code class="literal">GROUP BY</code>810        expressions are not included in the current grouping set.811        Bits are assigned with the rightmost argument corresponding to the812        least-significant bit; each bit is 0 if the corresponding expression813        is included in the grouping criteria of the grouping set generating814        the current result row, and 1 if it is not included.815       </p></td></tr></tbody></table></div></div><br class="table-break" /><p>816    The grouping operations shown in817    <a class="xref" href="functions-aggregate.html#FUNCTIONS-GROUPING-TABLE" title="Table 9.63. Grouping Operations">Table 9.63</a> are used in conjunction with818    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 distinguish819    result rows.  The arguments to the <code class="literal">GROUPING</code> function820    are not actually evaluated, but they must exactly match expressions given821    in the <code class="literal">GROUP BY</code> clause of the associated query level.822    For example:823</p><pre class="screen">824<code class="prompt">=&gt;</code> <strong class="userinput"><code>SELECT * FROM items_sold;</code></strong>825 make  | model | sales826-------+-------+-------827 Foo   | GT    |  10828 Foo   | Tour  |  20829 Bar   | City  |  15830 Bar   | Sport |  5831(4 rows)832 833<code class="prompt">=&gt;</code> <strong class="userinput"><code>SELECT make, model, GROUPING(make,model), sum(sales) FROM items_sold GROUP BY ROLLUP(make,model);</code></strong>834 make  | model | grouping | sum835-------+-------+----------+-----836 Foo   | GT    |        0 | 10837 Foo   | Tour  |        0 | 20838 Bar   | City  |        0 | 15839 Bar   | Sport |        0 | 5840 Foo   |       |        1 | 30841 Bar   |       |        1 | 20842       |       |        3 | 50843(7 rows)844</pre><p>845    Here, the <code class="literal">grouping</code> value <code class="literal">0</code> in the846    first four rows shows that those have been grouped normally, over both the847    grouping columns.  The value <code class="literal">1</code> indicates848    that <code class="literal">model</code> was not grouped by in the next-to-last two849    rows, and the value <code class="literal">3</code> indicates that850    neither <code class="literal">make</code> nor <code class="literal">model</code> was grouped851    by in the last row (which therefore is an aggregate over all the input852    rows).853   </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>
codekingpro/portable-devtools · Team Ai