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
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>SQLite Query Language: Aggregate Functions</title>
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_div("searchmenu")'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<span class="desktoponly">Search for:</span> <input type="text" name="q">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
</script>
</div>
<div class=nosearch><h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>Aggregate Functions</h2></div>
<p>
The aggregate functions shown below are available by default. Additional
aggregate functions written in C may be added using the
<a href="c3ref/create_function.html">sqlite3_create_function()</a></a>
API.</p>
<p>
In any aggregate function that takes a single argument, that argument
can be preceded by the keyword DISTINCT. In such cases, duplicate
elements are filtered before being passed into the aggregate function.
For example, the function "count(distinct X)" will return the number
of distinct values of column X instead of the total number of non-null
values in column X.
</p>
<style>
#listtab27 tr td {vertical-align:top;}
</style>
<table id='listtab27' width='100%'></table>
<script>
var listitems27 = [{"u":"lang_aggfunc.html#avg","x":"avg(X)","s":0},
{"u":"lang_aggfunc.html#count","x":"count(*)","s":0},
{"u":"lang_aggfunc.html#count","x":"count(X)","s":0},
{"u":"lang_aggfunc.html#groupconcat","x":"group_concat(X)","s":0},
{"u":"lang_aggfunc.html#groupconcat","x":"group_concat(X,Y)","s":0},
{"u":"lang_corefunc.html#maxoreunc","x":"max(X)","s":0},
{"u":"lang_corefunc.html#minoreunc","x":"min(X)","s":0},
{"u":"lang_aggfunc.html#sumunc","x":"sum(X)","s":0},
{"u":"lang_aggfunc.html#sumunc","x":"total(X)","s":0}];
var j = 0;
var w = Math.max(document.documentElement.clientWidth, window.innerWidth || 0);
var nCol = Math.floor(w/225);
if(nCol<=0) nCol=1;
var nRow = Math.ceil((listitems27.length+1)/nCol);
var h="<tr><td><ul class='multicol_list'>"
var ea
for(var i=0; i<listitems27.length; i++){
if( (++j)>nRow ){
h += "</ul></td>\n<td><ul class='multicol_list'>\n";
j = 1;
}
if(listitems27[i].u=="" || listitems27[i].s==2){
h += "<li>"
ea = ""
}else{
h += "<li><a href='";
h += listitems27[i].u;
h += "'>";
ea = "</a>"
}
if(listitems27[i].s==2 || listitems27[i].s==3) h += "<s>"
h += listitems27[i].x;
if(listitems27[i].s==2 || listitems27[i].s==3) h += "</s>"
h += ea
if(listitems27[i].s==1) h += "<small><i>(exp)</i></small>\n";
if(listitems27[i].s==3) h += "¹\n";
if(listitems27[i].s==4) h += "²\n";
if(listitems27[i].s==5) h += "³\n";
}
document.getElementById("listtab27").innerHTML = h;
</script>
<hr class='xhr'>
<dl>
<a name="avg"></a>
<dt><p><b>avg(<i>X</i>)</b></dt><dd><p>
The avg() function
returns the average value of all non-NULL <i>X</i> within a
group. String and BLOB values that do not look like numbers are
interpreted as 0.
The result of avg() is always a floating point value as long as
at there is at least one non-NULL input even if all
inputs are integers. The result of avg() is NULL if and only if
there are no non-NULL inputs.
</dd>
<a name="count"></a>
<dt><p><b>count(<i>X</i>)<br />count(*)</b></dt><dd><p>
The count(X) function returns
a count of the number of times
that <i>X</i> is not NULL in a group. The count(*) function
(with no arguments) returns the total number of rows in the group.
</dd>
<a name="groupconcat"></a>
<dt><p><b>group_concat(<i>X</i>)<br />group_concat(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
The group_concat() function returns
a string which is the concatenation of
all non-NULL values of <i>X</i>. If parameter <i>Y</i> is present then
it is used as the separator
between instances of <i>X</i>. A comma (",") is used as the separator
if <i>Y</i> is omitted. The order of the concatenated elements is
arbitrary.
</dd>
<a name="maxggunc"></a>
<dt><p><b>max(<i>X</i>)</b></dt><dd><p>
The max() aggregate function
returns the maximum value of all values in the group.
The maximum value is the value that would be returned last in an
ORDER BY on the same column. Aggregate max() returns NULL
if and only if there are no non-NULL values in the group.
</dd>
<a name="minggunc"></a>
<dt><p><b>min(<i>X</i>)</b></dt><dd><p>
The min() aggregate function
returns the minimum non-NULL value of all values in the group.
The minimum value is the first non-NULL value that would appear
in an ORDER BY of the column.
Aggregate min() returns NULL if and only if there are no non-NULL
values in the group.
</dd>
<a name="sumunc"></a>
<dt><p><b>sum(<i>X</i>)<br />total(<i>X</i>)</b></dt><dd><p>
The sum() and total() aggregate functions
return sum of all non-NULL values in the group.
If there are no non-NULL input rows then sum() returns
NULL but total() returns 0.0.
NULL is not normally a helpful result for the sum of no rows
but the SQL standard requires it and most other
SQL database engines implement sum() that way so SQLite does it in the
same way in order to be compatible. The non-standard total() function
is provided as a convenient way to work around this design problem
in the SQL language.</p>
<p>The result of total() is always a floating point value.
The result of sum() is an integer value if all non-NULL inputs are integers.
If any input to sum() is neither an integer or a NULL
then sum() returns a floating point value
which might be an approximation to the true sum.</p>
<p>Sum() will throw an "integer overflow" exception if all inputs
are integers or NULL
and an integer overflow occurs at any point during the computation.
Total() never throws an integer overflow.
</dd>
</dl>
|
