BlitzNG
/
database.mod
зеркало из https://github.com/bmx-ng/database.mod.git


			
				
					
						
						
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129
							<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<title>SQLite Query Language: Aggregate Functions</title>
<style type="text/css">
body {
    margin: auto;
    font-family: Verdana, sans-serif;
    padding: 8px 1%;
}

a { color: #45735f }
a:visited { color: #734559 }

.logo { position:absolute; margin:3px; }
.tagline {
  float:right;
  text-align:right;
  font-style:italic;
  width:240px;
  margin:12px;
  margin-top:58px;
}

.toolbar {
  font-variant: small-caps;
  text-align: center;
  line-height: 1.6em;
  margin: 0;
  padding:1px 8px;
}
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
.toolbar a:visited { color: white; }
.toolbar a:hover { color: #80a796; background: white; }

.content    { margin: 5%; }
.content dt { font-weight:bold; }
.content dd { margin-bottom: 25px; margin-left:20%; }
.content ul { padding:0px; padding-left: 15px; margin:0px; }

/* rounded corners */
.se  { background: url(images/se.png) 100% 100% no-repeat #80a796}
.sw  { background: url(images/sw.png) 0% 100% no-repeat }
.ne  { background: url(images/ne.png) 100% 0% no-repeat }
.nw  { background: url(images/nw.png) 0% 0% no-repeat }

</style>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
  
</head>
<body>
<div><!-- container div to satisfy validator -->

<a href="lang.html">
           <h2 align="center">SQL As Understood By SQLite</h2></a><h1>Aggregate Functions</h1>

<p>
The aggregate functions shown below are available by default. </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>

<table border=0 cellpadding=10>
<tr><td valign="top" align="right" width="120">avg(<i>X</i>)</td><td valign="top"><a name="avg"></a>

  Return 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.  
</td></tr><tr><td valign="top" align="right" width="120">count(<i>X</i>)<br></br>count(*)</td><td valign="top"><a name="count"></a>

  The first form return a count of the number of times
  that <i>X</i> is not NULL in a group.  The second form (with no argument)
  returns the total number of rows in the group.
</td></tr><tr><td valign="top" align="right" width="120">group_concat(<i>X</i>)<br></br>group_concat(<i>X</i>,<i>Y</i>)</td><td valign="top"><a name="group_concat"></a>

  The result is a string which is the concatenation of
  all non-NULL values of <i>X</i>.  If parameter <i>Y</i> is present then
  is 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.
</td></tr><tr><td valign="top" align="right" width="120">max(<i>X</i>)</td><td valign="top"><a name="maxAggFunc"></a>

  Return 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.  NULL is returned if and only if there are
  no non-NULL values in the group.
</td></tr><tr><td valign="top" align="right" width="120">min(<i>X</i>)</td><td valign="top"><a name="minAggFunc"></a>

  Return 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.
  NULL is only returned if and only if there are no non-NULL values in the 
  group.
</td></tr><tr><td valign="top" align="right" width="120">sum(<i>X</i>)<br></br>total(<i>X</i>)</td><td valign="top"><a name="sumFunc"></a>

  Return the numeric 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.
</td></tr>
</table>