codekingpro/portable-devtools
115k
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">=></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">=></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>