<p>The aggregate functions shown below are available by default.  Additionalaggregate functions written in C may be added using the <a href="capi3ref.html#sqlite3_create_function">sqlite3_create_function()</a>API.</p><p>In any aggregate function that takes a single argument, that argumentcan be preceeded by the keyword DISTINCT.  In such cases, duplicateelements are filtered before being passed into the aggregate function.For example, the function "count(distinct X)" will return the numberof distinct values of column X instead of the total number of non-nullvalues 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">Return the average value of all non-NULL <i>X</i> within agroup.  String and BLOB values that do not look like numbers areinterpreted as 0.The result of avg() is always a floating point value even if allinputs are integers. </p></td></tr><tr><td valign="top" align="right">count(<i>X</i>)<br>count(*)</td><td valign="top">The first form return a count of the number of timesthat <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">max(<i>X</i>)</td><td valign="top">Return the maximum value of all values in the group.The usual sort order is used to determine the maximum.</td></tr><tr><td valign="top" align="right">min(<i>X</i>)</td><td valign="top">Return the minimum non-NULL value of all values in the group.The usual sort order is used to determine the minimum.  NULL is only returnedif all values in the group are NULL.</td></tr><tr><td valign="top" align="right">sum(<i>X</i>)<br>total(<i>X</i>)</td><td valign="top">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 exception.</p></tr></table>}Section INSERT insertSyntax {sql-statement} {INSERT [OR <conflict-algorithm>] INTO [<database-name> .] <table-name> [(<column-list>)] VALUES(<value-list>) |INSERT [OR <conflict-algorithm>] INTO [<database-name> .] <table-name> [(<column-list>)] <select-statement>}puts {<p>The INSERT statement comes in two basic forms.  The first form(with the "VALUES" keyword) creates a single new row in an existing table.If no column-list is specified then the number of values mustbe the same as the number of columns in the table.  If a column-listis specified, then the number of values must match the number ofspecified columns.  Columns of the table that do not appear in thecolumn list are filled with the default value, or with NULL if notdefault value is specified.</p><p>The second form of the INSERT statement takes it data from aSELECT statement.  The number of columns in the result of theSELECT must exactly match the number of columns in the table ifno column list is specified, or it must match the number of columnsname in the column list.  A new entry is made in the tablefor every row of the SELECT result.  The SELECT may be simpleor compound.  If the SELECT statement has an ORDER BY clause,the ORDER BY is ignored.</p><p>The optional conflict-clause allows the specification of an alternativeconstraint conflict resolution algorithm to use during this one command.See the section titled<a href="#conflict">ON CONFLICT</a> for additional information.For compatibility with MySQL, the parser allows the use of thesingle keyword <a href="#replace">REPLACE</a> as an alias for "INSERT OR REPLACE".</p>}Section {ON CONFLICT clause} conflictSyntax {conflict-clause} {ON CONFLICT <conflict-algorithm>} {conflict-algorithm} {ROLLBACK | ABORT | FAIL | IGNORE | REPLACE}puts {<p>The ON CONFLICT clause is not a separate SQL command.  It is anon-standard clause that can appear in many other SQL commands.It is given its own section in this document because it is notpart of standard SQL and therefore might not be familiar.</p><p>The syntax for the ON CONFLICT clause is as shown above forthe CREATE TABLE command.  For the INSERT andUPDATE commands, the keywords "ON CONFLICT" are replaced by "OR", to makethe syntax seem more natural.  For example, instead of"INSERT ON CONFLICT IGNORE" we have "INSERT OR IGNORE".The keywords change but the meaning of the clause is the sameeither way.</p><p>The ON CONFLICT clause specifies an algorithm used to resolveconstraint conflicts.  There are five choices: ROLLBACK, ABORT,FAIL, IGNORE, and REPLACE. The default algorithm is ABORT.  Thisis what they mean:</p><dl><dt><b>ROLLBACK</b></dt><dd><p>When a constraint violation occurs, an immediate ROLLBACKoccurs, thus ending the current transaction, and the command abortswith a return code of SQLITE_CONSTRAINT.  If no transaction isactive (other than the implied transaction that is created on everycommand) then this algorithm works the same as ABORT.</p></dd><dt><b>ABORT</b></dt><dd><p>When a constraint violation occurs, the command backs outany prior changes it might have made and aborts with a return codeof SQLITE_CONSTRAINT.  But no ROLLBACK is executed so changesfrom prior commands within the same transactionare preserved.  This is the default behavior.</p></dd><dt><b>FAIL</b></dt><dd><p>When a constraint violation occurs, the command aborts with areturn code SQLITE_CONSTRAINT.  But any changes to the database thatthe command made prior to encountering the constraint violationare preserved and are not backed out.  For example, if an UPDATEstatement encountered a constraint violation on the 100th row thatit attempts to update, then the first 99 row changes are preservedbut changes to rows 100 and beyond never occur.</p></dd><dt><b>IGNORE</b></dt><dd><p>When a constraint violation occurs, the one row that containsthe constraint violation is not inserted or changed.  But the commandcontinues executing normally.  Other rows before and after the row thatcontained the constraint violation continue to be inserted or updatednormally.  No error is returned.</p></dd><dt><b>REPLACE</b></dt><dd><p>When a UNIQUE constraint violation occurs, the pre-existing rowsthat are causing the constraint violation are removed prior to insertingor updating the current row.  Thus the insert or update always occurs.The command continues executing normally.  No error is returned.If a NOT NULL constraint violation occurs, the NULL value is replacedby the default value for that column.  If the column has no defaultvalue, then the ABORT algorithm is used.  If a CHECK constraint violationoccurs then the IGNORE algorithm is used.</p><p>When this conflict resolution strategy deletes rows in order tosatisfy a constraint, it does not invoke delete triggers on thoserows.  This behavior might change in a future release.</p></dl><p>The algorithm specified in the OR clause of a INSERT or UPDATEoverrides any algorithm specified in a CREATE TABLE.If no algorithm is specified anywhere, the ABORT algorithm is used.</p>}Section REINDEX reindexSyntax {sql-statement} {  REINDEX <collation name>}Syntax {sql-statement} {  REINDEX [<database-name> .] <table/index-name>}puts {<p>The REINDEX command is used to delete and recreate indices from scratch.This is useful when the definition of a collation sequence has changed.</p><p>In the first form, all indices in all attached databases that use thenamed collation sequence are recreated. In the second form, if <i>[database-name.]table/index-name</i> identifies a table, then all indicesassociated with the table are rebuilt. If an index is identified, then onlythis specific index is deleted and recreated.</p><p>If no <i>database-name</i> is specified and there exists both a table orindex and a collation sequence of the specified name, then indices associatedwith the collation sequence only are reconstructed. This ambiguity may bedispelled by always specifying a <i>database-name</i> when reindexing aspecific table or index.}Section REPLACE replaceSyntax {sql-statement} {REPLACE INTO [<database-name> .] <table-name> [( <column-list> )] VALUES ( <value-list> ) |REPLACE INTO [<database-name> .] <table-name> [( <column-list> )] <select-statement>}puts {<p>The REPLACE command is an alias for the "INSERT OR REPLACE" variantof the <a href="#insert">INSERT</a> command.  This alias is provided forcompatibility with MySQL.  See the <a href="#insert">INSERT</a> command documentation for additionalinformation.</p>  }Section SELECT selectSyntax {sql-statement} {SELECT [ALL | DISTINCT] <result> [FROM <table-list>][WHERE <expr>][GROUP BY <expr-list>][HAVING <expr>][<compound-op> <select>]*[ORDER BY <sort-expr-list>][LIMIT <integer> [LP OFFSET | , RP <integer>]]} {result} {<result-column> [, <result-column>]*} {result-column} {STAR | <table-name> . STAR | <expr> [ [AS] <string> ]} {table-list} {<table> [<join-op> <table> <join-args>]*} {table} {<table-name> [AS <alias>] |( <select> ) [AS <alias>]} {join-op} {, | [NATURAL] [LEFT | RIGHT | FULL] [OUTER | INNER | CROSS] JOIN} {join-args} {[ON <expr>] [USING ( <id-list> )]} {sort-expr-list} {<expr> [<sort-order>] [, <expr> [<sort-order>]]*} {sort-order} {[ COLLATE <collation-name> ] [ ASC | DESC ]} {compound_op} {UNION | UNION ALL | INTERSECT | EXCEPT}puts {<p>The SELECT statement is used to query the database.  Theresult of a SELECT is zero or more rows of data where each rowhas a fixed number of columns.  The number of columns in theresult is specified by the expression list in between theSELECT and FROM keywords.  Any arbitrary expression can be usedas a result.  If a result expression is }puts "[Operator *] then all columns of all tables are substituted"puts {for that one expression.  If the expression is the name of}puts "a table followed by [Operator .*] then the result is all columns"puts {in that one table.</p><p>The DISTINCT keyword causes a subset of result rows to be returned, in which each result row is different.  NULL values are not treated as distinct from each other.  The default behavior is that all result rows be returned, which can be made explicit with the keyword ALL.</p><p>The query is executed against one or more tables specified afterthe FROM keyword.  If multiple tables names are separated by commas,then the query is against the cross join of the various tables.The full SQL-92 join syntax can also be used to specify joins.A sub-queryin parentheses may be substituted for any table name in the FROM clause.The entire FROM clause may be omitted, in which case the result is asingle row consisting of the values of the expression list.</p><p>The WHERE clause can be used to limit the number of rows overwhich the query operates.</p><p>The GROUP BY clauses causes one or more rows of the result tobe combined into a single row of output.  This is especially usefulwhen the result contains aggregate functions.  The expressions inthe GROUP BY clause do <em>not</em> have to be expressions thatappear in the result.  The HAVING clause is similar to WHERE exceptthat HAVING applies after grouping has occurred.  The HAVING expressionmay refer to values, even aggregate functions, that are not in the result.</p><p>The ORDER BY clause causes the output rows to be sorted.  The argument to ORDER BY is a list of expressions that are used as thekey for the sort.  The expressions do not have to be part of theresult for a simple SELECT, but in a compound SELECT each sortexpression must exactly match one of the result columns.  Eachsort expression may be optionally followed by a COLLATE keyword andthe name of a collating function used for ordering text and/orkeywords ASC or DESC to specify the sort order.</p><p>The LIMIT clause places an upper bound on the number of rowsreturned in the result.  A negative LIMIT indicates no upper bound.The optional OFFSET following LIMIT specifies how manyrows to skip at the beginning of the result set.In a compound query, the LIMIT clause may only appear on thefinal SELECT statement.The limit is applied to the entire query notto the individual SELECT statement to which it is attached.Note that if the OFFSET keyword is used in the LIMIT clause, then thelimit is the first number and the offset is the second number.  If acomma is used instead of the OFFSET keyword, then the offset is thefirst number and the limit is the second number.  This seemingcontradition is intentional - it maximizes compatibility with legacySQL database systems.</p><p>A compound SELECT is formed from two or more simple SELECTs connectedby one of the operators UNION, UNION ALL, INTERSECT, or EXCEPT.  Ina compound SELECT, all the constituent SELECTs must specify thesame number of result columns.  There may be only a single ORDER BYclause at the end of the compound SELECT.  The UNION and UNION ALLoperators combine the results of the SELECTs to the right and left intoa single big table.  The difference is that in UNION all result rowsare distinct where in UNION ALL there may be duplicates.The INTERSECT operator takes the intersection of the results of the