DBISAM 3.26

Sum(TotalGp / TotalSell) as GpPercentage

The calculation is nowhere close to being accurate.

?

John

"John Easley" <someone@somewhere.com> wrote in message
news:F03138E2-0B7B-4ED3-B5C8-DE39FC5D6DF2@news.elevatesoft.com...
> DBISAM 3.26
>
> Sum(TotalGp / TotalSell) as GpPercentage
>
> The calculation is nowhere close to being accurate.

define "accurate"

robert

>
> ?
>
> John
>

> define "accurate"

Some examples...

TotalSell = $1,549,486
TotalGp = $384,663
GpPercentage = 36.91
Should be 24.82

TotalSell = $1,477
TotalGp = $1,313
GpPercentage = 2.40
Should be 88.89

John

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:B8C477F1-BD14-4364-894B-2B010FADB369@news.elevatesoft.com...
>
> "John Easley" <someone@somewhere.com> wrote in message
> news:F03138E2-0B7B-4ED3-B5C8-DE39FC5D6DF2@news.elevatesoft.com...
>> DBISAM 3.26
>>
>> Sum(TotalGp / TotalSell) as GpPercentage
>>
>> The calculation is nowhere close to being accurate.
>
>
> robert
>
>>
>> ?
>>
>> John
>>
>
>

"John Easley" <someone@somewhere.com> wrote in message
news:A82CE8B8-9D55-468B-B211-B94D10365D2F@news.elevatesoft.com...
>> define "accurate"
>
> Some examples...
>
> TotalSell = $1,549,486
> TotalGp = $384,663
> GpPercentage = 36.91
> Should be 24.82
>

Your  logic is wrong. Adding up all the individual percentages is not the
way to get the percentage of the whole file. Say you have two rows (cols are
sell, gp and percentage)

1,549,000 384,600 25
486   63 13

Since the first row represents most of the total, your overall file percent
will be close to 25.

Robert

> Your  logic is wrong. Adding up all the individual percentages is not the
> way to get the percentage of the whole file. Say you have two rows (cols
> are sell, gp and percentage)
>
> 1,549,000 384,600 25
> 486   63 13
>
> Since the first row represents most of the total, your overall file
> percent will be close to 25.

My samples were rows in the result set, not rows in the query.

I don't think it's summing the GpPercentage field, because the numbers would
be much higher.

john

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:6048155A-6385-4716-8405-7656E390482D@news.elevatesoft.com...
>
> "John Easley" <someone@somewhere.com> wrote in message
> news:A82CE8B8-9D55-468B-B211-B94D10365D2F@news.elevatesoft.com...
>>> define "accurate"
>>
>> Some examples...
>>
>> TotalSell = $1,549,486
>> TotalGp = $384,663
>> GpPercentage = 36.91
>> Should be 24.82
>>
>
>
> Robert
>
>

"John Easley" <someone@somewhere.com> wrote in message
news:DF0C7581-7D77-44E0-9259-6D8CA8BFB46A@news.elevatesoft.com...
>> Your  logic is wrong. Adding up all the individual percentages is not the
>> way to get the percentage of the whole file. Say you have two rows (cols
>> are sell, gp and percentage)
>>
>> 1,549,000 384,600 25
>> 486   63 13
>>
>> Since the first row represents most of the total, your overall file
>> percent will be close to 25.
>
> My samples were rows in the result set, not rows in the query.
>

Yeah, so I split your first result into a small two row table input to your
query, to illustrate what you're doing wrong. A two row table like the one I
describe would generate in your query the same sell and gp totals as your
data, but the percentage would be nonsense.

> I don't think it's summing the GpPercentage field, because the numbers
> would be much higher.
>

It's possible there is a bug in DBISAM v3, but I seriously doubt it. Easy
enough to prove, just run the query without summing the fields, display all
individual fields and add up the percentages by hand (or if you generate too
many rows, select into a memory table and then sum the memory table fields
using SQL). Regardless, your logic is wrong.

Robert

> john
>
> "Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
> news:6048155A-6385-4716-8405-7656E390482D@news.elevatesoft.com...
>>
>> "John Easley" <someone@somewhere.com> wrote in message
>> news:A82CE8B8-9D55-468B-B211-B94D10365D2F@news.elevatesoft.com...
>>>> define "accurate"
>>>
>>> Some examples...
>>>
>>> TotalSell = $1,549,486
>>> TotalGp = $384,663
>>> GpPercentage = 36.91
>>> Should be 24.82
>>>
>>
>>
>> Robert
>>
>>
>
>

What do you get if you use:

   Sum(TotalGp) / Sum(TotalSell) * 100

(Assuming each record has a TotalGp and a TotalSell)

> What do you get if you use:
>
> Sum(TotalGp) / Sum(TotalSell) * 100

I get an error.. 'aggregate functions cannot be used within expressions'

John

"Malcolm" <malcolm@spam.will.bounce> wrote in message
news:3E70FA37-6169-402B-853E-3B6D0E157057@news.elevatesoft.com...
> What do you get if you use:
>
> Sum(TotalGp) / Sum(TotalSell) * 100
>
> (Assuming each record has a TotalGp and a TotalSell)
>

You need to sum them into an intermediate (usually memory) table, then do
the math on the totals.

Robert