Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

sum of a money datatype off by one cent

4 posts in General Discussion Last posting was on 2011-02-19 17:34:35.0Z
Robert Densmore Posted on 2011-02-19 17:05:59.0Z
From: Robert Densmore <bdensmore@austin.rr.ignore.com>
Newsgroups: sybase.public.ase.general
Subject: sum of a money datatype off by one cent
Message-ID: <69tvl6dlhohvgtc9j6s5qdn5bf2iqno82r@4ax.com>
X-Newsreader: Forte Agent 1.93/32.576 English (American)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 19 Feb 2011 09:05:59 -0800
X-Trace: forums-1-dub 1298135159 10.22.241.152 (19 Feb 2011 09:05:59 -0800)
X-Original-Trace: 19 Feb 2011 09:05:59 -0800, vip152.sybase.com
Lines: 38
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29992
Article PK: 79221

Shouldn't the sum of a smallmoney datatype column return accurate
results?

I have a table with a smallmoney datatype that a sum over the entire
set, yields a value that is one cent off.

select sum(c1) from #t1

Yields 4362.05 and the correct answer is 4362.06.

The count of distinct values of column c1 money are:

select c1, count(*)
from #t1
group by c1

c1 count
------------------------ -----------
21.16 102
11.13 198

(21.16 * 102) + (11.13 * 198) = 4362.06

ASE Version:Adaptive Server Enterprise/15.0.3/EBF 17273 ESD#2

Name Owner Object_type Create_date
---- ----- ----------- -----------
#t100001180009860649 dbo user table Feb 19 2011 10:58AM

Column_name Type Length Prec Scale Nulls Default_name Rule_name
Access_Rule_name Computed_Column_object Identity
----------- ---- ------ ---- ----- ----- ------------ ---------
---------------- ---------------------- --------
c1 smallmoney 4 NULL NULL 0 NULL NULL
NULL NULL 0

Thanks,
Bob


"Mark A. Parsons" <iron_horse Posted on 2011-02-19 17:30:12.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: sum of a money datatype off by one cent
References: <69tvl6dlhohvgtc9j6s5qdn5bf2iqno82r@4ax.com>
In-Reply-To: <69tvl6dlhohvgtc9j6s5qdn5bf2iqno82r@4ax.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d5ffe24$1@forums-1-dub>
Date: 19 Feb 2011 09:30:12 -0800
X-Trace: forums-1-dub 1298136612 10.22.241.152 (19 Feb 2011 09:30:12 -0800)
X-Original-Trace: 19 Feb 2011 09:30:12 -0800, vip152.sybase.com
Lines: 65
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29993
Article PK: 79222

Since [small]money can hold up to 4 digits of accuracy my first question would be ... do all c1 values have only 2
decimal places of accuracy?

What do you get if you queries display 4 digits of accuracy, eg:

========================
select convert(numeric(10,4),sum(c1))
from #t1
go

select convert(numeric(10,4),c1),count(*)
from #t1
group by convert(numeric(10,4),c1)
go

select sum(a*b)
from
(select convert(numeric(10,4),c1),count(*)
from #t1
group by convert(numeric(10,4),c1)
) dt (a,b)
go
========================

On 02/19/2011 12:05, Robert Densmore wrote:
> Shouldn't the sum of a smallmoney datatype column return accurate
> results?
>
> I have a table with a smallmoney datatype that a sum over the entire
> set, yields a value that is one cent off.
>
> select sum(c1) from #t1
>
> Yields 4362.05 and the correct answer is 4362.06.
>
> The count of distinct values of column c1 money are:
>
> select c1, count(*)
> from #t1
> group by c1
>
> c1 count
> ------------------------ -----------
> 21.16 102
> 11.13 198
>
> (21.16 * 102) + (11.13 * 198) = 4362.06
>
> ASE Version:Adaptive Server Enterprise/15.0.3/EBF 17273 ESD#2
>
> Name Owner Object_type Create_date
> ---- ----- ----------- -----------
> #t100001180009860649 dbo user table Feb 19 2011 10:58AM
>
> Column_name Type Length Prec Scale Nulls Default_name Rule_name
> Access_Rule_name Computed_Column_object Identity
> ----------- ---- ------ ---- ----- ----- ------------ ---------
> ---------------- ---------------------- --------
> c1 smallmoney 4 NULL NULL 0 NULL NULL
> NULL NULL 0
>
> Thanks,
> Bob


Robert Densmore Posted on 2011-02-19 17:34:35.0Z
From: Robert Densmore <bdensmore@austin.rr.ignore.com>
Newsgroups: sybase.public.ase.general
Subject: Re: sum of a money datatype off by one cent
Message-ID: <rmvvl65fsl65k2lq1u1v9k16g6dnrhurue@4ax.com>
References: <69tvl6dlhohvgtc9j6s5qdn5bf2iqno82r@4ax.com> <4d5ffe24$1@forums-1-dub>
X-Newsreader: Forte Agent 1.93/32.576 English (American)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 19 Feb 2011 09:34:35 -0800
X-Trace: forums-1-dub 1298136875 10.22.241.152 (19 Feb 2011 09:34:35 -0800)
X-Original-Trace: 19 Feb 2011 09:34:35 -0800, vip152.sybase.com
Lines: 75
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29995
Article PK: 79230

Thanks Mark. That's it. Some of the data had internally 4 digits of
accuracy.

Bob


On 19 Feb 2011 09:30:12 -0800, "Mark A. Parsons"

<iron_horse@no_spamola.compuserve.com> wrote:

>Since [small]money can hold up to 4 digits of accuracy my first question would be ... do all c1 values have only 2
>decimal places of accuracy?
>
>What do you get if you queries display 4 digits of accuracy, eg:
>
>========================
>select convert(numeric(10,4),sum(c1))
>from #t1
>go
>
>select convert(numeric(10,4),c1),count(*)
>from #t1
>group by convert(numeric(10,4),c1)
>go
>
>select sum(a*b)
>from
> (select convert(numeric(10,4),c1),count(*)
> from #t1
> group by convert(numeric(10,4),c1)
> ) dt (a,b)
>go
>========================
>
>
>
>On 02/19/2011 12:05, Robert Densmore wrote:
>> Shouldn't the sum of a smallmoney datatype column return accurate
>> results?
>>
>> I have a table with a smallmoney datatype that a sum over the entire
>> set, yields a value that is one cent off.
>>
>> select sum(c1) from #t1
>>
>> Yields 4362.05 and the correct answer is 4362.06.
>>
>> The count of distinct values of column c1 money are:
>>
>> select c1, count(*)
>> from #t1
>> group by c1
>>
>> c1 count
>> ------------------------ -----------
>> 21.16 102
>> 11.13 198
>>
>> (21.16 * 102) + (11.13 * 198) = 4362.06
>>
>> ASE Version:Adaptive Server Enterprise/15.0.3/EBF 17273 ESD#2
>>
>> Name Owner Object_type Create_date
>> ---- ----- ----------- -----------
>> #t100001180009860649 dbo user table Feb 19 2011 10:58AM
>>
>> Column_name Type Length Prec Scale Nulls Default_name Rule_name
>> Access_Rule_name Computed_Column_object Identity
>> ----------- ---- ------ ---- ----- ----- ------------ ---------
>> ---------------- ---------------------- --------
>> c1 smallmoney 4 NULL NULL 0 NULL NULL
>> NULL NULL 0
>>
>> Thanks,
>> Bob


Robert Densmore Posted on 2011-02-19 17:30:34.0Z
From: Robert Densmore <bdensmore@austin.rr.ignore.com>
Newsgroups: sybase.public.ase.general
Subject: Re: sum of a money datatype off by one cent
Message-ID: <ecvvl6tmneu8dp4b73bstlk2q2g9502kp3@4ax.com>
References: <69tvl6dlhohvgtc9j6s5qdn5bf2iqno82r@4ax.com>
X-Newsreader: Forte Agent 1.93/32.576 English (American)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 19 Feb 2011 09:30:34 -0800
X-Trace: forums-1-dub 1298136634 10.22.241.152 (19 Feb 2011 09:30:34 -0800)
X-Original-Trace: 19 Feb 2011 09:30:34 -0800, vip152.sybase.com
Lines: 49
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29994
Article PK: 79225

Nevermind, I see that the value of c1 for the 21.16 values are
actually stored as 21.1599. I need to ensure that the values get
written to the table as 21.16 instead of 21.1599.

Thanks,
Bob

On 19 Feb 2011 09:05:59 -0800, Robert Densmore

<bdensmore@austin.rr.ignore.com> wrote:

>Shouldn't the sum of a smallmoney datatype column return accurate
>results?
>
>I have a table with a smallmoney datatype that a sum over the entire
>set, yields a value that is one cent off.
>
>select sum(c1) from #t1
>
>Yields 4362.05 and the correct answer is 4362.06.
>
>The count of distinct values of column c1 money are:
>
>select c1, count(*)
>from #t1
>group by c1
>
>c1 count
>------------------------ -----------
> 21.16 102
> 11.13 198
>
>(21.16 * 102) + (11.13 * 198) = 4362.06
>
>ASE Version:Adaptive Server Enterprise/15.0.3/EBF 17273 ESD#2
>
>Name Owner Object_type Create_date
>---- ----- ----------- -----------
>#t100001180009860649 dbo user table Feb 19 2011 10:58AM
>
>Column_name Type Length Prec Scale Nulls Default_name Rule_name
>Access_Rule_name Computed_Column_object Identity
>----------- ---- ------ ---- ----- ----- ------------ ---------
>---------------- ---------------------- --------
>c1 smallmoney 4 NULL NULL 0 NULL NULL
>NULL NULL 0
>
>Thanks,
>Bob