SQLite total vs sum

Peter Haworth pete at lcsql.com
Sat Jul 4 12:18:58 EDT 2015


I lot depends on your application and whether you might want to move to
another SQL implementation in the future.  If you think you might move, I
would define a default value of zero for the columns you plan on summing
and use (sum).  That still leaves the issue of integer overflow but you can
probably work out if that is likely to be an issue for your application.

If you're 100% sure you won;t move to another SQL implementation, I'd go
with total().


On Sat, Jul 4, 2015 at 1:06 AM Terence Heaford <t.heaford at icloud.com> wrote:

> 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
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>



More information about the use-livecode mailing list