SQLite total vs sum

[Terence Heaford]

I have come across this situation in an app and the solution seems to be to use total(X) rather than sum(X).

Comments on the correct way forward from any sqlite aficionados please. I suppose if necessary I could test the value returned by sum(X) but it does seem easier to use total(X).

The reason for the question is because in the sqlite blurb refers to total(X) as non-standard.

"sum(X)
total(X)
The sum() and total() aggregate functions return 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.

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.
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 integer overflow."


All the best

Terry