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