SQLite has a function called sum() and another function called total(). Both functions do pretty much the same thing – they return the sum of all non-NULL values in a group.

But there is a subtle difference between these two functions.

The difference is in the way they handle NULL inputs.

The Difference

Here’s where these two functions differ.

If there are no non-NULL input rows:

• sum() returns NULL
• total() returns 0.0

Example

Here’s an example to demonstrate the difference between the SQLite sum() and total() functions.

SELECT 
  sum(NULL),
  total(NULL);

Result:

sum(NULL)   total(NULL)
----------  -----------
            0.0        

The first column is blank, because it’s NULL.

Why the Difference?

The reason for having two functions that do the same thing except for this one difference is that, sum() is fully standards compliant. The SQL standard requires that the sum() function return NULL when its input is NULL.

Returning NULL might be standards compliant, but it’s not particularly useful if you’re expecting a number.

The total() function has therefore been provided as a more useful alternative to sum().

You can choose which function you prefer to use, depending on how you want the function to deal with NULL values (and how standards-compliant you prefer to be).

Your decision might also be influenced by how portable you want your code to be. sum() seems to be more commonly used function name in other major DBMSs.