Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | SUM never gives an error. An integer result is returned for exact results and a floating point result is returned for approximate results. Tickets #1664, #1669, and #1670. (CVS 3066) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
9e04f8fdf1ec0dc36effb55c05d075b3 |
User & Date: | drh 2006-02-09 22:13:42.000 |
Context
2006-02-09
| ||
22:24 | Allow '@' to introduce host parameter names for compatibility with MS SQL Server. Ticket #1671. (CVS 3067) (check-in: 0738ef818d user: drh tags: trunk) | |
22:13 | SUM never gives an error. An integer result is returned for exact results and a floating point result is returned for approximate results. Tickets #1664, #1669, and #1670. (CVS 3066) (check-in: 9e04f8fdf1 user: drh tags: trunk) | |
18:35 | Deprecate the sqlite3_aggregate_count() API. Make sure all aggregate counters are 64 bits. Remove unused StdDev structure from func.c. Ticket #1669. (CVS 3065) (check-in: 44bd7ba432 user: drh tags: trunk) | |
Changes
Changes to src/func.c.
︙ | ︙ | |||
12 13 14 15 16 17 18 | ** This file contains the C functions that implement various SQL ** functions of SQLite. ** ** There is only one exported symbol in this file - the function ** sqliteRegisterBuildinFunctions() found at the bottom of the file. ** All other code has file scope. ** | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | ** This file contains the C functions that implement various SQL ** functions of SQLite. ** ** There is only one exported symbol in this file - the function ** sqliteRegisterBuildinFunctions() found at the bottom of the file. ** All other code has file scope. ** ** $Id: func.c,v 1.121 2006/02/09 22:13:42 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* #include <math.h> */ #include <stdlib.h> #include <assert.h> #include "vdbeInt.h" |
︙ | ︙ | |||
815 816 817 818 819 820 821 | ** An instance of the following structure holds the context of a ** sum() or avg() aggregate computation. */ typedef struct SumCtx SumCtx; struct SumCtx { LONGDOUBLE_TYPE sum; /* Sum of terms */ i64 cnt; /* Number of elements summed */ | | > > > > > > > > > | < | > > > > > > > | < | < < | | 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 | ** An instance of the following structure holds the context of a ** sum() or avg() aggregate computation. */ typedef struct SumCtx SumCtx; struct SumCtx { LONGDOUBLE_TYPE sum; /* Sum of terms */ i64 cnt; /* Number of elements summed */ u8 approx; /* True if sum is approximate */ }; /* ** Routines used to compute the sum, average, and total. ** ** The SUM() function follows the (broken) SQL standard which means ** that it returns NULL if it sums over no inputs. TOTAL returns ** 0.0 in that case. In addition, TOTAL always returns a float where ** SUM might return an integer if it never encounters a floating point ** value. ** ** I am told that SUM() should raise an exception if it encounters ** a integer overflow. But after pondering this, I decided that ** behavior leads to brittle programs. So instead, I have coded ** SUM() to revert to using floating point if it encounters an ** integer overflow. The answer may not be exact, but it will be ** close. If the SUM() function returns an integer, the value is ** exact. If SUM() returns a floating point value, it means the ** value might be approximated. */ static void sumStep(sqlite3_context *context, int argc, sqlite3_value **argv){ SumCtx *p; int type; assert( argc==1 ); p = sqlite3_aggregate_context(context, sizeof(*p)); type = sqlite3_value_numeric_type(argv[0]); if( p && type!=SQLITE_NULL ){ p->cnt++; if( type==SQLITE_INTEGER ){ p->sum += sqlite3_value_int64(argv[0]); if( !p->approx ){ i64 iVal; p->approx = p->sum!=(LONGDOUBLE_TYPE)(iVal = (i64)p->sum); } }else{ p->sum += sqlite3_value_double(argv[0]); p->approx = 1; } } } static void sumFinalize(sqlite3_context *context){ SumCtx *p; p = sqlite3_aggregate_context(context, 0); if( p && p->cnt>0 ){ if( p->approx ){ sqlite3_result_double(context, p->sum); }else{ sqlite3_result_int64(context, (i64)p->sum); } } } static void avgFinalize(sqlite3_context *context){ SumCtx *p; p = sqlite3_aggregate_context(context, 0); if( p && p->cnt>0 ){ |
︙ | ︙ |
Changes to src/sqlite.h.in.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This header file defines the interface that the SQLite library ** presents to client programs. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This header file defines the interface that the SQLite library ** presents to client programs. ** ** @(#) $Id: sqlite.h.in,v 1.160 2006/02/09 22:13:42 drh Exp $ */ #ifndef _SQLITE3_H_ #define _SQLITE3_H_ #include <stdarg.h> /* Needed for the definition of va_list */ /* ** Make sure we can call this stuff from C++. |
︙ | ︙ | |||
894 895 896 897 898 899 900 901 902 903 904 905 906 907 | int sqlite3_column_bytes16(sqlite3_stmt*, int iCol); double sqlite3_column_double(sqlite3_stmt*, int iCol); int sqlite3_column_int(sqlite3_stmt*, int iCol); sqlite_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol); const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol); const void *sqlite3_column_text16(sqlite3_stmt*, int iCol); int sqlite3_column_type(sqlite3_stmt*, int iCol); /* ** The sqlite3_finalize() function is called to delete a compiled ** SQL statement obtained by a previous call to sqlite3_prepare() ** or sqlite3_prepare16(). If the statement was executed successfully, or ** not executed at all, then SQLITE_OK is returned. If execution of the ** statement failed then an error code is returned. | > | 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 | int sqlite3_column_bytes16(sqlite3_stmt*, int iCol); double sqlite3_column_double(sqlite3_stmt*, int iCol); int sqlite3_column_int(sqlite3_stmt*, int iCol); sqlite_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol); const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol); const void *sqlite3_column_text16(sqlite3_stmt*, int iCol); int sqlite3_column_type(sqlite3_stmt*, int iCol); int sqlite3_column_numeric_type(sqlite3_stmt*, int iCol); /* ** The sqlite3_finalize() function is called to delete a compiled ** SQL statement obtained by a previous call to sqlite3_prepare() ** or sqlite3_prepare16(). If the statement was executed successfully, or ** not executed at all, then SQLITE_OK is returned. If execution of the ** statement failed then an error code is returned. |
︙ | ︙ | |||
1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 | int sqlite3_value_int(sqlite3_value*); sqlite_int64 sqlite3_value_int64(sqlite3_value*); const unsigned char *sqlite3_value_text(sqlite3_value*); const void *sqlite3_value_text16(sqlite3_value*); const void *sqlite3_value_text16le(sqlite3_value*); const void *sqlite3_value_text16be(sqlite3_value*); int sqlite3_value_type(sqlite3_value*); /* ** Aggregate functions use the following routine to allocate ** a structure for storing their state. The first time this routine ** is called for a particular aggregate, a new structure of size nBytes ** is allocated, zeroed, and returned. On subsequent calls (for the ** same aggregate instance) the same buffer is returned. The implementation | > | 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 | int sqlite3_value_int(sqlite3_value*); sqlite_int64 sqlite3_value_int64(sqlite3_value*); const unsigned char *sqlite3_value_text(sqlite3_value*); const void *sqlite3_value_text16(sqlite3_value*); const void *sqlite3_value_text16le(sqlite3_value*); const void *sqlite3_value_text16be(sqlite3_value*); int sqlite3_value_type(sqlite3_value*); int sqlite3_value_numeric_type(sqlite3_value*); /* ** Aggregate functions use the following routine to allocate ** a structure for storing their state. The first time this routine ** is called for a particular aggregate, a new structure of size nBytes ** is allocated, zeroed, and returned. On subsequent calls (for the ** same aggregate instance) the same buffer is returned. The implementation |
︙ | ︙ |
Changes to src/test_md5.c.
︙ | ︙ | |||
349 350 351 352 353 354 355 | */ int Md5_Init(Tcl_Interp *interp){ Tcl_CreateCommand(interp, "md5", (Tcl_CmdProc*)md5_cmd, 0, 0); Tcl_CreateCommand(interp, "md5file", (Tcl_CmdProc*)md5file_cmd, 0, 0); return TCL_OK; } | < < < | 349 350 351 352 353 354 355 356 357 358 359 360 361 362 | */ int Md5_Init(Tcl_Interp *interp){ Tcl_CreateCommand(interp, "md5", (Tcl_CmdProc*)md5_cmd, 0, 0); Tcl_CreateCommand(interp, "md5file", (Tcl_CmdProc*)md5file_cmd, 0, 0); return TCL_OK; } /* ** During testing, the special md5sum() aggregate function is available. ** inside SQLite. The following routines implement that function. */ static void md5step(sqlite3_context *context, int argc, sqlite3_value **argv){ MD5Context *p; int i; |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
39 40 41 42 43 44 45 | ** ** Various scripts scan this source file in order to generate HTML ** documentation, headers files, or other derived files. The formatting ** of the code in this file is, therefore, important. See other comments ** in this file for details. If in doubt, do not deviate from existing ** commenting and indentation practices when changing or adding code. ** | | | 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | ** ** Various scripts scan this source file in order to generate HTML ** documentation, headers files, or other derived files. The formatting ** of the code in this file is, therefore, important. See other comments ** in this file for details. If in doubt, do not deviate from existing ** commenting and indentation practices when changing or adding code. ** ** $Id: vdbe.c,v 1.542 2006/02/09 22:13:42 drh Exp $ */ #include "sqliteInt.h" #include "os.h" #include <ctype.h> #include "vdbeInt.h" /* |
︙ | ︙ | |||
184 185 186 187 188 189 190 191 192 193 194 195 196 197 | } p->apCsr[iCur] = pCx = sqliteMalloc( sizeof(Cursor) ); if( pCx ){ pCx->iDb = iDb; } return pCx; } /* ** Processing is determine by the affinity parameter: ** ** SQLITE_AFF_INTEGER: ** SQLITE_AFF_REAL: ** SQLITE_AFF_NUMERIC: | > > > > > > > > > > > > > > > > > > > > > > > > > | 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 217 218 219 220 221 222 | } p->apCsr[iCur] = pCx = sqliteMalloc( sizeof(Cursor) ); if( pCx ){ pCx->iDb = iDb; } return pCx; } /* ** Try to convert a value into a numeric representation if we can ** do so without loss of information. In other words, if the string ** looks like a number, convert it into a number. If it does not ** look like a number, leave it alone. */ static void applyNumericAffinity(Mem *pRec){ if( (pRec->flags & (MEM_Real|MEM_Int))==0 ){ int realnum; sqlite3VdbeMemNulTerminate(pRec); if( (pRec->flags&MEM_Str) && sqlite3IsNumber(pRec->z, &realnum, pRec->enc) ){ i64 value; sqlite3VdbeChangeEncoding(pRec, SQLITE_UTF8); if( !realnum && sqlite3atoi64(pRec->z, &value) ){ sqlite3VdbeMemRelease(pRec); pRec->i = value; pRec->flags = MEM_Int; }else{ sqlite3VdbeMemRealify(pRec); } } } } /* ** Processing is determine by the affinity parameter: ** ** SQLITE_AFF_INTEGER: ** SQLITE_AFF_REAL: ** SQLITE_AFF_NUMERIC: |
︙ | ︙ | |||
216 217 218 219 220 221 222 | if( 0==(pRec->flags&MEM_Str) && (pRec->flags&(MEM_Real|MEM_Int)) ){ sqlite3VdbeMemStringify(pRec, enc); } pRec->flags &= ~(MEM_Real|MEM_Int); }else if( affinity!=SQLITE_AFF_NONE ){ assert( affinity==SQLITE_AFF_INTEGER || affinity==SQLITE_AFF_REAL || affinity==SQLITE_AFF_NUMERIC ); | < < < < < < < < < < < < | < | < | | | < < | | > > > > > > > > > > > > > | 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 | if( 0==(pRec->flags&MEM_Str) && (pRec->flags&(MEM_Real|MEM_Int)) ){ sqlite3VdbeMemStringify(pRec, enc); } pRec->flags &= ~(MEM_Real|MEM_Int); }else if( affinity!=SQLITE_AFF_NONE ){ assert( affinity==SQLITE_AFF_INTEGER || affinity==SQLITE_AFF_REAL || affinity==SQLITE_AFF_NUMERIC ); applyNumericAffinity(pRec); if( pRec->flags & MEM_Real ){ sqlite3VdbeIntegerAffinity(pRec); } } } /* ** Try to convert the type of a function argument or a result column ** into a numeric representation. Use either INTEGER or REAL whichever ** is appropriate. But only do the conversion if it is possible without ** loss of information and return the revised type of the argument. ** ** This is an EXPERIMENTAL api and is subject to change or removal. */ int sqlite3_value_numeric_type(sqlite3_value *pVal){ Mem *pMem = (Mem*)pVal; applyNumericAffinity(pMem); storeTypeInfo(pMem, 0); return pMem->type; } /* ** Exported version of applyAffinity(). This one works on sqlite3_value*, ** not the internal Mem* type. */ void sqlite3ValueApplyAffinity(sqlite3_value *pVal, u8 affinity, u8 enc){ applyAffinity((Mem *)pVal, affinity, enc); |
︙ | ︙ |
Changes to src/vdbeapi.c.
︙ | ︙ | |||
70 71 72 73 74 75 76 77 78 79 80 81 82 83 | const void *sqlite3_value_text16le(sqlite3_value *pVal){ return sqlite3ValueText(pVal, SQLITE_UTF16LE); } #endif /* SQLITE_OMIT_UTF16 */ int sqlite3_value_type(sqlite3_value* pVal){ return pVal->type; } /**************************** sqlite3_result_ ******************************* ** The following routines are used by user-defined functions to specify ** the function result. */ void sqlite3_result_blob( sqlite3_context *pCtx, | > | 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 | const void *sqlite3_value_text16le(sqlite3_value *pVal){ return sqlite3ValueText(pVal, SQLITE_UTF16LE); } #endif /* SQLITE_OMIT_UTF16 */ int sqlite3_value_type(sqlite3_value* pVal){ return pVal->type; } /* sqlite3_value_numeric_type() defined in vdbe.c */ /**************************** sqlite3_result_ ******************************* ** The following routines are used by user-defined functions to specify ** the function result. */ void sqlite3_result_blob( sqlite3_context *pCtx, |
︙ | ︙ | |||
464 465 466 467 468 469 470 471 472 473 474 475 476 477 | columnMallocFailure(pStmt); return val; } #endif /* SQLITE_OMIT_UTF16 */ int sqlite3_column_type(sqlite3_stmt *pStmt, int i){ return sqlite3_value_type( columnMem(pStmt,i) ); } /* ** Convert the N-th element of pStmt->pColName[] into a string using ** xFunc() then return that string. If N is out of range, return 0. ** ** There are up to 5 names for each column. useType determines which ** name is returned. Here are the names: | > > > > > > > | 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 | columnMallocFailure(pStmt); return val; } #endif /* SQLITE_OMIT_UTF16 */ int sqlite3_column_type(sqlite3_stmt *pStmt, int i){ return sqlite3_value_type( columnMem(pStmt,i) ); } /* The following function is experimental and subject to change or ** removal */ /*int sqlite3_column_numeric_type(sqlite3_stmt *pStmt, int i){ ** return sqlite3_value_numeric_type( columnMem(pStmt,i) ); **} */ /* ** Convert the N-th element of pStmt->pColName[] into a string using ** xFunc() then return that string. If N is out of range, return 0. ** ** There are up to 5 names for each column. useType determines which ** name is returned. Here are the names: |
︙ | ︙ |
Changes to test/func.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing built-in functions. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing built-in functions. # # $Id: func.test,v 1.47 2006/02/09 22:13:42 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table to work with. # do_test func-0.0 { |
︙ | ︙ | |||
547 548 549 550 551 552 553 | CREATE TABLE t6(x INTEGER); INSERT INTO t6 VALUES(1); INSERT INTO t6 VALUES(1<<62); SELECT sum(x) - ((1<<62)+1) from t6; } } 0 | | > > | | | 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 | CREATE TABLE t6(x INTEGER); INSERT INTO t6 VALUES(1); INSERT INTO t6 VALUES(1<<62); SELECT sum(x) - ((1<<62)+1) from t6; } } 0 # Ticket #1669, #1670: I am told that if an integer overflow occurs # during a sum that the result should be an error. This strikes me # as being brittle. So I'm not doing it that way. # making the SQL-standard version SUM() even more useless than it # was before. # # The non-standard TOTAL() function continues to give a helpful result. # do_test func-18.11 { execsql { SELECT typeof(sum(x)) FROM t6 } } integer do_test func-18.12 { execsql { INSERT INTO t6 VALUES(1<<62); SELECT sum(x) - ((1<<62)*2.0+1) from t6; } } {0.0} do_test func-18.13 { execsql { SELECT total(x) - ((1<<62)*2.0+1) FROM t6 } } 0.0 finish_test |
Changes to test/select1.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the SELECT statement. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the SELECT statement. # # $Id: select1.test,v 1.48 2006/02/09 22:13:42 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Try to select on a non-existant table. # do_test select1-1.1 { |
︙ | ︙ | |||
207 208 209 210 211 212 213 | } {1 {wrong number of arguments to function sum()}} do_test select1-2.17 { set v [catch {execsql {SELECT SUM(f1)+1 FROM test1}} msg] lappend v $msg } {0 45} do_test select1-2.17.1 { execsql {SELECT sum(a) FROM t3} | | | 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 | } {1 {wrong number of arguments to function sum()}} do_test select1-2.17 { set v [catch {execsql {SELECT SUM(f1)+1 FROM test1}} msg] lappend v $msg } {0 45} do_test select1-2.17.1 { execsql {SELECT sum(a) FROM t3} } {44.0} do_test select1-2.18 { set v [catch {execsql {SELECT XYZZY(f1) FROM test1}} msg] lappend v $msg } {1 {no such function: XYZZY}} do_test select1-2.19 { set v [catch {execsql {SELECT SUM(min(f1,f2)) FROM test1}} msg] lappend v $msg |
︙ | ︙ | |||
824 825 826 827 828 829 830 | (SELECT a FROM abc WHERE a = NULL AND b >= upper.c) ) FROM abc AS upper; } } {0} } finish_test | < | 824 825 826 827 828 829 830 | (SELECT a FROM abc WHERE a = NULL AND b >= upper.c) ) FROM abc AS upper; } } {0} } finish_test |
Changes to www/lang.tcl.
1 2 3 | # # Run this Tcl script to generate the lang-*.html files. # | | | 1 2 3 4 5 6 7 8 9 10 11 | # # Run this Tcl script to generate the lang-*.html files. # set rcsid {$Id: lang.tcl,v 1.108 2006/02/09 22:13:42 drh Exp $} source common.tcl if {[llength $argv]>0} { set outputdir [lindex $argv 0] } else { set outputdir "" } |
︙ | ︙ | |||
1362 1363 1364 1365 1366 1367 1368 | 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">Return the average value of all non-NULL <i>X</i> within a | > | > > | 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 | 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">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 even if all inputs 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 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> |
︙ | ︙ | |||
1387 1388 1389 1390 1391 1392 1393 | <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 returned if 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> | | | | | | | | | | > > > > > > > > | 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 | <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 returned if 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 and the sum is exact. If any input to sum() is neither an integer or a NULL or if the an integer overflow occurs at any point during the computation, then sum() returns a floating point value which might be an approximation to the true sum.</td> </tr> </table> } Section INSERT insert |
︙ | ︙ |