D. Richard Hipp wrote:
> On Jan 29, 2009, at 10:40 AM, matkinson wrote:
>
>
>> Hi,
>>
>> I'm not replying with an answer, but with a similar problem. I am
>> using a
>> TRAC database on top of a SQLite DB and I want to provide a
>> percentage of
>> the sum total hours/sum estimated hours. Here's what I'm seeing.
>> - when only one value (a natural number) is summed and divided, the
>> answer
>> is 0 (should be 73/100=73%).
>> - when multiple lines are summed and divided && the some of the
>> lines have
>> fractions (like 7.25, 1.33, etc.), then the answer is correct.
>> - when multiple lines are summed and divided && the lines are all
>> natural
>> numbers, then the answer is 0.
>>
>> My query is below. Does this make any sense?
>>
>
> No, it makes no sense. But it does conform to the SQL standard. If
> you want standards compliance use SUM(). If, on the other hand, you
> want a sensible answer, use the TOTAL() function instead of SUM().
>
>
>
>
Sure, it makes perfect sense. :-)
Sqlite does integer division with integer arguments, and floating point
division if either of the arguments are a floating point value.
sqlite> select 1/3;
0
sqlite> select 1.0/3;
0.333333333333333
The SUM function produces an exact integer result if all its arguments
are exact integers. If any of the arguments to SUM are approximate (i.e.
floating point) values then SUM produces an approximate (i.e. floating
point) result.
sqlite> select SUM(1);
1
sqlite> select SUM(1.0);
1.0
When these two behaviors are combined you get the results you are seeing.
sqlite> select SUM(1)/3;
0
sqlite> select SUM(1.0)/3;
0.333333333333333
To get the results you expect, you can use the non-standard TOTAL
function as Richard suggested. It is the same as SUM except it always
returns an approximate floating point result, and hence results in
floating point division.
sqlite> select TOTAL(1);
1.0
sqlite> select TOTAL(1)/3;
0.333333333333333
Alternatively, if you want to use SQL that is more portable, you could
also cast the result of the SUM function to a floating point value to
ensure that a floating point division is done.
sqlite> select cast(SUM(1) as real)/3;
0.333333333333333
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
