Index: src/func.c
==================================================================
--- src/func.c
+++ src/func.c
@@ -14,11 +14,11 @@
 **
 ** 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.115 2006/01/09 16:12:05 danielk1977 Exp $
+** $Id: func.c,v 1.116 2006/01/12 22:17:50 drh Exp $
 */
 #include "sqliteInt.h"
 #include <ctype.h>
 /* #include <math.h> */
 #include <stdlib.h>
@@ -821,11 +821,17 @@
   int cnt;        /* Number of elements summed */
   u8 seenFloat;   /* True if there has been any floating point value */
 };
 
 /*
-** Routines used to compute the sum or average.
+** 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.
 */
 static void sumStep(sqlite3_context *context, int argc, sqlite3_value **argv){
   SumCtx *p;
   int type;
   assert( argc==1 );
@@ -854,10 +860,15 @@
   SumCtx *p;
   p = sqlite3_aggregate_context(context, 0);
   if( p && p->cnt>0 ){
     sqlite3_result_double(context, p->sum/(double)p->cnt);
   }
+}
+static void totalFinalize(sqlite3_context *context){
+  SumCtx *p;
+  p = sqlite3_aggregate_context(context, 0);
+  sqlite3_result_double(context, p ? p->sum : 0.0);
 }
 
 /*
 ** An instance of the following structure holds the context of a
 ** variance or standard deviation computation.
@@ -998,10 +1009,11 @@
     void (*xFinalize)(sqlite3_context*);
   } aAggs[] = {
     { "min",    1, 0, 1, minmaxStep,   minMaxFinalize },
     { "max",    1, 2, 1, minmaxStep,   minMaxFinalize },
     { "sum",    1, 0, 0, sumStep,      sumFinalize    },
+    { "total",  1, 0, 0, sumStep,      totalFinalize    },
     { "avg",    1, 0, 0, sumStep,      avgFinalize    },
     { "count",  0, 0, 0, countStep,    countFinalize  },
     { "count",  1, 0, 0, countStep,    countFinalize  },
   };
   int i;

Index: test/null.test
==================================================================
--- test/null.test
+++ test/null.test
@@ -99,10 +99,18 @@
   execsql {
     select count(*), count(b), count(c), sum(b), sum(c), 
            avg(b), avg(c), min(b), max(b) from t1;
   }
 } {7 4 6 2 3 0.5 0.5 0 1}
+
+# The sum of zero entries is a NULL, but the total of zero entries is 0.
+#
+do_test null-3.2 {
+  execsql {
+    SELECT sum(b), total(b) FROM t1 WHERE b<0
+  }
+} {{} 0.0}
 
 # Check to see how WHERE clauses handle NULL values.  A NULL value
 # is the same as UNKNOWN.  The WHERE clause should only select those
 # rows that are TRUE.  FALSE and UNKNOWN rows are rejected.
 #

Index: www/lang.tcl
==================================================================
--- www/lang.tcl
+++ www/lang.tcl
@@ -1,9 +1,9 @@
 #
 # Run this Tcl script to generate the lang-*.html files.
 #
-set rcsid {$Id: lang.tcl,v 1.104 2006/01/04 15:58:29 drh Exp $}
+set rcsid {$Id: lang.tcl,v 1.105 2006/01/12 22:17:50 drh Exp $}
 source common.tcl
 
 if {[llength $argv]>0} {
   set outputdir [lindex $argv 0]
 } else {
@@ -1393,19 +1393,19 @@
 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>)</td>
+<td valign="top" align="right">sum(<i>X</i>)<br>total(<i>X</i>)</td>
 <td valign="top">Return the numeric sum of all numeric values in the group.
-   If there are no input rows or all values are NULL, then NULL is returned.
+   If there are no input rows or all values are NULL, then sum() returns
+   NULL but total() returns zero.
    NULL is not a helpful result in that case (the correct answer should be
-   zero) but it is what the SQL standard requires and how 
-   most other SQL database engines operate so SQLite does it that way
-   in order to be compatible. 
-   You will probably want to use
-   "<b>coalesce(sum(</b>X<b>),0)</b>" instead of just "<b>sum(</b>X<b>)</b>"
+   zero) but the SQL standard requires that behavior from sum() and that is how 
+   most other SQL database engines implement sum() so SQLite does it that 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.</td>
 </tr>
 </table>
 }