In SQLite, the total() function returns the sum of all non-NULL values in a group.

If there are no non-NULL values, then it returns 0.0.

This function is similar to the sum() function, except in the way it handles NULL inputs. When there are no non-NULL values, then sum() returns NULL (instead of 0.0 like the total() function returns).

Example

Consider the following table.

SELECT * FROM Products;

Result:

ProductId   ProductName            Price     
----------  ---------------------  ----------
1           Blue Widgets (6 Pack)  389.45    
2           Widget Holder          139.5     
3           Widget Opener          89.27     
4           Foobar Set             120.0     
5           Red Widget                    

We can use the total() function to get a total of the prices.

SELECT total(Price) FROM Products;

Result:

738.22

Even though the last row’s price is NULL, the total() function can handle that, because it adds all the non-NULL values and ignores the NULL value.

All NULL Values

If there are no non-NULL values, then the result is 0.0.

SELECT total(Price) 
FROM Products
WHERE ProductId = 5;

Result:

0.0      

This is where total() and sum() differ. In this case, sum() would have returned NULL.

The total() function always returns a floating point value.

Bear in mind that this does not conform to the SQL standard though. If you want to use standard compliant code, use sum().

Get the Total of a Subset

Here’s another example that uses a WHERE clause to return a subset of the table.

SELECT total(Price) 
FROM Products
WHERE ProductId > 2;

Result:

209.27

This time it’s adding non-NULL values, so I get a non-NULL result.

Here’s another one that filters by the same column that I’m adding.

SELECT total(Price) 
FROM Products
WHERE Price < 150;

Result:

348.77