Decimal division results do not round as expected

Say that the values of Sev3Met = 1 and the value of Sev3Total = 2
Using this
round((Sev3Met/(Sev3Total + 0.0)),2),
I would expect .50
However when I code in SQL 2005 Express manager against a SQL 2005
database, the results display as 0.5000000000
Using this
round(Sev3Met/cast(Sev3Total as decimal(3,0)),2)
I get 0.500000
is there some setting I am missing? How can I get 0.50 'Try something like:
CONVERT(DECIMAL(5.2),<expression you want to display with 2 decimals> )
Roy Harvey
Beacon Falls, CT
On 28 Jun 2006 14:30:31 -0700, wxbuff@.aol.com wrote:

>Say that the values of Sev3Met = 1 and the value of Sev3Total = 2
>Using this
>round((Sev3Met/(Sev3Total + 0.0)),2),
>I would expect .50
>However when I code in SQL 2005 Express manager against a SQL 2005
>database, the results display as 0.5000000000
>Using this
>round(Sev3Met/cast(Sev3Total as decimal(3,0)),2)
>I get 0.500000
>is there some setting I am missing? How can I get 0.50 '|||<wxbuff@.aol.com> wrote in message
news:1151530231.287935.312370@.m73g2000cwd.googlegroups.com...
> Say that the values of Sev3Met = 1 and the value of Sev3Total = 2
> Using this
> round((Sev3Met/(Sev3Total + 0.0)),2),
> I would expect .50
> However when I code in SQL 2005 Express manager against a SQL 2005
> database, the results display as 0.5000000000
> Using this
> round(Sev3Met/cast(Sev3Total as decimal(3,0)),2)
> I get 0.500000
> is there some setting I am missing? How can I get 0.50 '
>
Here are the rules for how the precision and scale of decimals is increased
through arithmetic.
Precision, Scale, and Length
http://msdn.microsoft.com/library/d...br />
8rc5.asp
David|||convert( decimal(3,2), round(Sev3Met/cast(Sev3Total as decimal(3,0)),2) )
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<wxbuff@.aol.com> wrote in message
news:1151530231.287935.312370@.m73g2000cwd.googlegroups.com...
> Say that the values of Sev3Met = 1 and the value of Sev3Total = 2
> Using this
> round((Sev3Met/(Sev3Total + 0.0)),2),
> I would expect .50
> However when I code in SQL 2005 Express manager against a SQL 2005
> database, the results display as 0.5000000000
> Using this
> round(Sev3Met/cast(Sev3Total as decimal(3,0)),2)
> I get 0.500000
> is there some setting I am missing? How can I get 0.50 '
>|||It actually does round it to two decimal places. It's just displaying extra
zeroes. Try this to confirm:
DECLARE @.Sev3Met NUMERIC(10, 5)
DECLARE @.Sev3Total NUMERIC(10, 5)
SELECT @.Sev3Met = 1.0
SELECT @.Sev3Total = 3.0
SELECT round((@.Sev3Met/(@.Sev3Total + 0.0)),2)
i.e., Make Sev3Total = 3 instead of 2. The result is:
.33000000000000000
The extra zeroes are a display issue.
<wxbuff@.aol.com> wrote in message
news:1151530231.287935.312370@.m73g2000cwd.googlegroups.com...
> Say that the values of Sev3Met = 1 and the value of Sev3Total = 2
> Using this
> round((Sev3Met/(Sev3Total + 0.0)),2),
> I would expect .50
> However when I code in SQL 2005 Express manager against a SQL 2005
> database, the results display as 0.5000000000
> Using this
> round(Sev3Met/cast(Sev3Total as decimal(3,0)),2)
> I get 0.500000
> is there some setting I am missing? How can I get 0.50 '
>

decimal division precision

in my understanding of fixed numeric types and datatype precision rules,
dividing two decimals with identical precision/scale should result in a
decimal outcome with the same precision/scale. however, i run the following
in query analyzer:
declare @.price decimal(38,10)
declare @.mult decimal(38,10)
set @.price = 5.2347551174
set @.mult = 0.01
select @.price / @.mult
RESULT> 523.475511
if i convert @.mult to a float this all works as expected. whats going on her
e?
many thanks
kh> in my understanding of fixed numeric types and datatype precision rules,
> dividing two decimals with identical precision/scale should result in a
> decimal outcome with the same precision/scale
Precision, Scale, and Length
http://msdn.microsoft.com/library/d...br />
8rc5.asp
Try using a lower precision.
declare @.price decimal(18,10)
declare @.mult decimal(18,10)
set @.price = 5.2347551174
set @.mult = 0.01
select @.price / @.mult
go
Here is a very interesting script, from Steve Kass, to see the p and s of
the result.
http://www.microsoft.com/technet/co...>
6&sloc=en-us
AMB
"kh" wrote:

> in my understanding of fixed numeric types and datatype precision rules,
> dividing two decimals with identical precision/scale should result in a
> decimal outcome with the same precision/scale. however, i run the followin
g
> in query analyzer:
> declare @.price decimal(38,10)
> declare @.mult decimal(38,10)
> set @.price = 5.2347551174
> set @.mult = 0.01
> select @.price / @.mult
> RESULT> 523.475511
> if i convert @.mult to a float this all works as expected. whats going on h
ere?
> many thanks
> kh
>|||lovely. thanks.
kh
"Alejandro Mesa" wrote:
> Precision, Scale, and Length
> http://msdn.microsoft.com/library/d... />
b_8rc5.asp
> Try using a lower precision.
> declare @.price decimal(18,10)
> declare @.mult decimal(18,10)
> set @.price = 5.2347551174
> set @.mult = 0.01
> select @.price / @.mult
> go
> Here is a very interesting script, from Steve Kass, to see the p and s of
> the result.
> http://www.microsoft.com/technet/co...
c46&sloc=en-us
>
> AMB
>
> "kh" wrote:
>|||Another way to make it work, without changing the precision of your base
numbers...
Specifically cast your result, either in your SQL statement, or assign it to
a variable which has the proper precision.
declare @.price decimal(38,10)
declare @.mult decimal(38,10)
declare @.Result decimal(38,10)
set @.price = 5.2347551174
set @.mult = 0.01
set @.Result = @.price / @.mult
select @.price / @.mult
, cast(@.price / @.mult as decimal(38,10))
, @.Result
go
"kh" <kh@.newsgroups.nospam> wrote in message
news:92EE84D6-BEB4-419A-9054-76EF39B69608@.microsoft.com...
> lovely. thanks.
> kh
> "Alejandro Mesa" wrote:
>
rules,
a
http://msdn.microsoft.com/library/d..._da-db_8rc5.asp[
color=darkred]
of
http://www.microsoft.com/technet/co...5c46&sloc=en-us[color=darkr
ed]
rules,
a
following
on here?|||Jim,
Don't understand, the least significant digits are still truncated and
stuffed with 0s in your solution.
Anyways. Try this and check out the results. Is there a pattern that I am
not able to keep my finger on... there are only 6 places of decimal. or
rather ... I dunno.
P.S: Jim, Chance for u to fuel your ego :)
declare @.price decimal(38,10)
declare @.mult1 decimal(38,10)
declare @.mult2 decimal(38,10)
declare @.mult3 decimal(38,10)
set @.price = 5.2347551174
set @.mult1 = 0.01
set @.mult2 = 0.001
set @.mult3 = 0.0001
select @.price / @.mult1,
@.price / @.mult2,
@.price / @.mult3|||So they are...
Not sure what I was thinking when I looked at that yesterday. I would have
sworn the digits were there, but clearly they are not.
I guess the moral of the story is not simply casting everything when you do
math in SQL Server, but insuring that you only use the precision you
actually need.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:651C58FC-11D3-480D-A185-DDC6CDB1E5DF@.microsoft.com...
> Jim,
> Don't understand, the least significant digits are still truncated and
> stuffed with 0s in your solution.
> Anyways. Try this and check out the results. Is there a pattern that I am
> not able to keep my finger on... there are only 6 places of decimal. or
> rather ... I dunno.
> P.S: Jim, Chance for u to fuel your ego :)
> declare @.price decimal(38,10)
> declare @.mult1 decimal(38,10)
> declare @.mult2 decimal(38,10)
> declare @.mult3 decimal(38,10)
>
> set @.price = 5.2347551174
> set @.mult1 = 0.01
> set @.mult2 = 0.001
> set @.mult3 = 0.0001
>
> select @.price / @.mult1,
> @.price / @.mult2,
> @.price / @.mult3
>

Decimal Division

Hi,

I'm trying to divide two decimal(19,4) numbers but it keeps giving me a Divide by 0 error.

- 10698.25 / 76782.11 = -0.13 (but I get a Divide by 0)

I looked it up in the Sql Server docs, does the Divide by 0 error also cover Stack overflows?

If so, how do I get around this?

Many thanks,
StuartSELECT - 10698.25 / 76782.11 works for me. Why don't you post the malfunctioning code.