1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>SQLite Query Language: Aggregate Functions</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
120
<h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>Aggregate Functions</h2>
123
The aggregate functions shown below are available by default. Additional
124
aggregate functions written in C may be added using the
125
<a href="c3ref/create_function.html">sqlite3_create_function()</a></a>
129
In any aggregate function that takes a single argument, that argument
130
can be preceded by the keyword DISTINCT. In such cases, duplicate
131
elements are filtered before being passed into the aggregate function.
132
For example, the function "count(distinct X)" will return the number
133
of distinct values of column X instead of the total number of non-null
137
<table border=0 cellpadding=10>
138
<tr><td valign="top" align="right" width="120"><a name="avg"></a>
139
avg(<i>X</i>)</td><td valign="top">
141
returns the average value of all non-NULL <i>X</i> within a
142
group. String and BLOB values that do not look like numbers are
144
The result of avg() is always a floating point value as long as
145
at there is at least one non-NULL input even if all
146
inputs are integers. The result of avg() is NULL if and only if
147
there are no non-NULL inputs.
148
</td></tr><tr><td valign="top" align="right" width="120"><a name="count"></a>
149
count(<i>X</i>)<br></br>count(*)</td><td valign="top">
150
The count(X) function returns
151
a count of the number of times
152
that <i>X</i> is not NULL in a group. The count(*) function
153
(with no arguments) returns the total number of rows in the group.
154
</td></tr><tr><td valign="top" align="right" width="120"><a name="groupconcat"></a>
155
group_concat(<i>X</i>)<br></br>group_concat(<i>X</i>,<i>Y</i>)</td><td valign="top">
156
The group_concat() function returns
157
a string which is the concatenation of
158
all non-NULL values of <i>X</i>. If parameter <i>Y</i> is present then
159
it is used as the separator
160
between instances of <i>X</i>. A comma (",") is used as the separator
161
if <i>Y</i> is omitted. The order of the concatenated elements is
163
</td></tr><tr><td valign="top" align="right" width="120"><a name="maxggunc"></a>
164
max(<i>X</i>)</td><td valign="top">
165
The max() aggregate function
166
returns the maximum value of all values in the group.
167
The maximum value is the value that would be returned last in an
168
ORDER BY on the same column. Aggregate max() returns NULL
169
if and only if there are no non-NULL values in the group.
170
</td></tr><tr><td valign="top" align="right" width="120"><a name="minggunc"></a>
171
min(<i>X</i>)</td><td valign="top">
172
The min() aggregate function
173
returns the minimum non-NULL value of all values in the group.
174
The minimum value is the first non-NULL value that would appear
175
in an ORDER BY of the column.
176
Aggregate min() returns NULL if and only if there are no non-NULL
178
</td></tr><tr><td valign="top" align="right" width="120"><a name="sumunc"></a>
179
sum(<i>X</i>)<br></br>total(<i>X</i>)</td><td valign="top">
180
The sum() and total() aggregate functions
181
return sum of all non-NULL values in the group.
182
If there are no non-NULL input rows then sum() returns
183
NULL but total() returns 0.0.
184
NULL is not normally a helpful result for the sum of no rows
185
but the SQL standard requires it and most other
186
SQL database engines implement sum() that way so SQLite does it in the
187
same way in order to be compatible. The non-standard total() function
188
is provided as a convenient way to work around this design problem
189
in the SQL language.</p>
191
<p>The result of total() is always a floating point value.
192
The result of sum() is an integer value if all non-NULL inputs are integers.
193
If any input to sum() is neither an integer or a NULL
194
then sum() returns a floating point value
195
which might be an approximation to the true sum.</p>
197
<p>Sum() will throw an "integer overflow" exception if all inputs
199
and an integer overflow occurs at any point during the computation.
200
Total() never throws an integer overflow.
added
removed
lang_aggfunc.html
