1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>9.15.Aggregate Functions</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="functions.html" title="Chapter9.Functions and Operators">
<link rel="prev" href="functions-array.html" title="9.14.Array Functions and Operators">
<link rel="next" href="functions-subquery.html" title="9.16.Subquery Expressions">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="functions-aggregate"></a>9.15.Aggregate Functions</h2></div></div></div>
<a name="id618943"></a><p> <em class="firstterm">Aggregate functions</em> compute a single result
value from a set of input values. <a href="functions-aggregate.html#functions-aggregate-table" title="Table9.37.Aggregate Functions">Table9.37, “Aggregate Functions”</a> shows the built-in aggregate
functions. The special syntax considerations for aggregate
functions are explained in <a href="sql-expressions.html#syntax-aggregates" title="4.2.7.Aggregate Expressions">Section4.2.7, “Aggregate Expressions”</a>.
Consult <a href="tutorial-agg.html" title="2.7.Aggregate Functions">Section2.7, “Aggregate Functions”</a> for additional introductory
information.
</p>
<div class="table">
<a name="functions-aggregate-table"></a><p class="title"><b>Table9.37.Aggregate Functions</b></p>
<div class="table-contents"><table summary="Aggregate Functions" border="1">
<colgroup>
<col>
<col>
<col>
<col>
</colgroup>
<thead><tr>
<th>Function</th>
<th>Argument Type</th>
<th>Return Type</th>
<th>Description</th>
</tr></thead>
<tbody>
<tr>
<td> <a name="id619020"></a>
<code class="function">avg(<em class="replaceable"><code>expression</code></em>)</code>
</td>
<td> <code class="type">smallint</code>, <code class="type">int</code>,
<code class="type">bigint</code>, <code class="type">real</code>, <code class="type">double
precision</code>, <code class="type">numeric</code>, or <code class="type">interval</code>
</td>
<td> <code class="type">numeric</code> for any integer type argument,
<code class="type">double precision</code> for a floating-point argument,
otherwise the same as the argument data type
</td>
<td>the average (arithmetic mean) of all input values</td>
</tr>
<tr>
<td> <a name="id619090"></a>
<code class="function">bit_and(<em class="replaceable"><code>expression</code></em>)</code>
</td>
<td> <code class="type">smallint</code>, <code class="type">int</code>, <code class="type">bigint</code>, or
<code class="type">bit</code>
</td>
<td> same as argument data type
</td>
<td>the bitwise AND of all non-null input values, or null if none</td>
</tr>
<tr>
<td> <a name="id619139"></a>
<code class="function">bit_or(<em class="replaceable"><code>expression</code></em>)</code>
</td>
<td> <code class="type">smallint</code>, <code class="type">int</code>, <code class="type">bigint</code>, or
<code class="type">bit</code>
</td>
<td> same as argument data type
</td>
<td>the bitwise OR of all non-null input values, or null if none</td>
</tr>
<tr>
<td> <a name="id619188"></a>
<code class="function">bool_and(<em class="replaceable"><code>expression</code></em>)</code>
</td>
<td> <code class="type">bool</code>
</td>
<td> <code class="type">bool</code>
</td>
<td>true if all input values are true, otherwise false</td>
</tr>
<tr>
<td> <a name="id619228"></a>
<code class="function">bool_or(<em class="replaceable"><code>expression</code></em>)</code>
</td>
<td> <code class="type">bool</code>
</td>
<td> <code class="type">bool</code>
</td>
<td>true if at least one input value is true, otherwise false</td>
</tr>
<tr>
<td><code class="function">count(*)</code></td>
<td></td>
<td><code class="type">bigint</code></td>
<td>number of input values</td>
</tr>
<tr>
<td><code class="function">count(<em class="replaceable"><code>expression</code></em>)</code></td>
<td>any</td>
<td><code class="type">bigint</code></td>
<td> number of input values for which the value of <em class="replaceable"><code>expression</code></em> is not null
</td>
</tr>
<tr>
<td> <a name="id619315"></a>
<code class="function">every(<em class="replaceable"><code>expression</code></em>)</code>
</td>
<td> <code class="type">bool</code>
</td>
<td> <code class="type">bool</code>
</td>
<td>equivalent to <code class="function">bool_and</code>
</td>
</tr>
<tr>
<td><code class="function">max(<em class="replaceable"><code>expression</code></em>)</code></td>
<td>any array, numeric, string, or date/time type</td>
<td>same as argument type</td>
<td> maximum value of <em class="replaceable"><code>expression</code></em> across all input
values
</td>
</tr>
<tr>
<td><code class="function">min(<em class="replaceable"><code>expression</code></em>)</code></td>
<td>any array, numeric, string, or date/time type</td>
<td>same as argument type</td>
<td> minimum value of <em class="replaceable"><code>expression</code></em> across all input
values
</td>
</tr>
<tr>
<td> <a name="id619420"></a>
<code class="function">stddev(<em class="replaceable"><code>expression</code></em>)</code>
</td>
<td> <code class="type">smallint</code>, <code class="type">int</code>,
<code class="type">bigint</code>, <code class="type">real</code>, <code class="type">double
precision</code>, or <code class="type">numeric</code>
</td>
<td> <code class="type">double precision</code> for floating-point arguments,
otherwise <code class="type">numeric</code>
</td>
<td>sample standard deviation of the input values</td>
</tr>
<tr>
<td><code class="function">sum(<em class="replaceable"><code>expression</code></em>)</code></td>
<td> <code class="type">smallint</code>, <code class="type">int</code>,
<code class="type">bigint</code>, <code class="type">real</code>, <code class="type">double
precision</code>, <code class="type">numeric</code>, or
<code class="type">interval</code>
</td>
<td> <code class="type">bigint</code> for <code class="type">smallint</code> or
<code class="type">int</code> arguments, <code class="type">numeric</code> for
<code class="type">bigint</code> arguments, <code class="type">double precision</code>
for floating-point arguments, otherwise the same as the
argument data type
</td>
<td>sum of <em class="replaceable"><code>expression</code></em> across all input values</td>
</tr>
<tr>
<td> <a name="id619569"></a>
<code class="function">variance</code>(<em class="replaceable"><code>expression</code></em>)
</td>
<td> <code class="type">smallint</code>, <code class="type">int</code>,
<code class="type">bigint</code>, <code class="type">real</code>, <code class="type">double
precision</code>, or <code class="type">numeric</code>
</td>
<td> <code class="type">double precision</code> for floating-point arguments,
otherwise <code class="type">numeric</code>
</td>
<td>sample variance of the input values (square of the sample standard deviation)</td>
</tr>
</tbody>
</table></div>
</div>
<br class="table-break"><p> It should be noted that except for <code class="function">count</code>,
these functions return a null value when no rows are selected. In
particular, <code class="function">sum</code> of no rows returns null, not
zero as one might expect. The <code class="function">coalesce</code> function may be
used to substitute zero for null when necessary.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Note</h3>
<a name="id619659"></a><a name="id619665"></a><p> Boolean aggregates <code class="function">bool_and</code> and
<code class="function">bool_or</code> correspond to standard SQL aggregates
<code class="function">every</code> and <code class="function">any</code> or
<code class="function">some</code>.
As for <code class="function">any</code> and <code class="function">some</code>,
it seems that there is an ambiguity built into the standard syntax:
</p>
<pre class="programlisting">SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;</pre>
<p>
Here <code class="function">ANY</code> can be considered both as leading
to a subquery or as an aggregate if the select expression returns 1 row.
Thus the standard name cannot be given to these aggregates.
</p>
</div>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Note</h3>
<p> Users accustomed to working with other SQL database management
systems may be surprised by the performance of the
<code class="function">count</code> aggregate when it is applied to the
entire table. A query like:
</p>
<pre class="programlisting">SELECT count(*) FROM sometable;</pre>
<p>
will be executed by <span class="productname">PostgreSQL</span> using a
sequential scan of the entire table.
</p>
</div>
</div></body>
</html>
|
