CAST (AS DECIMAL(30,5)) Behaving Differently

Question

CAST (AS DECIMAL(30,5)) Behaving Differently

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
September 28, 2012 at 1:07 am

#267396

Hi,
I had written the lines of code as
Cast(SUM(A*B) / SUM(C) AS DECIMAL(30,5)) and
Cast(SUM(A*B) AS DECIMAL(30,5))/Cast(SUM(C) AS DECIMAL(30,5))
Conceptually both should return the same result , which is not the case now. Can anybody explain this
phenomena.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

I'm never sure if this is a bug or not in SQL server, but for some reason if the Divisor is an Integer, the result will also be an Integer.

If you case the Divisor then you will get another different result.

Declare @a int =1

, @b-2 int =2

, @C int=3

Select CAST((@a*@b)/(@c) as DECIMAL(30,5))

Select (@a*@b)/CAST(@c as DECIMAL(30,5))

Select CAST((@a*@b) as Decimal(30,5))/CAsT(@c as DECIMAL(30,5))

_________________________________________________________________________
SSC Guide to Posting and Best Practices

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Shadab Shah (9/28/2012)
Hi,
I had written the lines of code as
Cast(SUM(A*B) / SUM(C) AS DECIMAL(30,5)) and
Cast(SUM(A*B) AS DECIMAL(30,5))/Cast(SUM(C) AS DECIMAL(30,5))
Conceptually both should return the same result , which is not the case now. Can anybody explain this
phenomena.

How about posting numbers A, B and C?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 3

It's truncation of intermediate results.

Using Decimal data type will fix it:

-------------------------------------------------------------------------

DECLARE @a Int, @b-2 Int, @C Int;

SELECT @a = 9,

@b-2 = 3,

@C = 2;

SELECT Cast(SUM(@A*@B) / SUM(@C) AS DECIMAL(30,5));

SELECT Cast(SUM(@A*@B) AS DECIMAL(30,5))/Cast(SUM(@C) AS DECIMAL(30,5));

SELECT SUM(@A*@B), SUM(@A*@B) / SUM(@C);

-------------------------------------------------------------------------

DECLARE @A2 Decimal(18,4), @b2 Decimal(18,4), @c2 Decimal(18,4);

SELECT @A2 = 9,

@b2 = 3,

@c2 = 2;

SELECT Cast(SUM(@A2*@B2) / SUM(@C2) AS DECIMAL(30,5));

SELECT Cast(SUM(@A2*@B2) AS DECIMAL(30,5))/Cast(SUM(@C2) AS DECIMAL(30,5));

SELECT SUM(@A2*@B2), SUM(@A2*@B2) / SUM(@C2);

-------------------------------------------------------------------------

DECLARE @A3 Decimal(30,5), @B3 Decimal(30,5), @C3 Decimal(30,5);

SELECT @A3 = 9,

@B3 = 3,

@C3 = 2;

SELECT SUM(@A3*@B3) / SUM(@C3);

-------------------------------------------------------------------------

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

laurie-789651 (9/28/2012)
It's truncation of intermediate results.
Using Decimal data type will fix it:
-------------------------------------------------------------------------
DECLARE @a Int, @b-2 Int, @C Int;
SELECT @a = 9,
@b-2 = 3,
@C = 2;
SELECT Cast(SUM(@A*@B) / SUM(@C) AS DECIMAL(30,5));
SELECT Cast(SUM(@A*@B) AS DECIMAL(30,5))/Cast(SUM(@C) AS DECIMAL(30,5));
SELECT SUM(@A*@B), SUM(@A*@B) / SUM(@C);
-------------------------------------------------------------------------
DECLARE @A2 Decimal(18,4), @b2 Decimal(18,4), @c2 Decimal(18,4);
SELECT @A2 = 9,
@b2 = 3,
@c2 = 2;
SELECT Cast(SUM(@A2*@B2) / SUM(@C2) AS DECIMAL(30,5));
SELECT Cast(SUM(@A2*@B2) AS DECIMAL(30,5))/Cast(SUM(@C2) AS DECIMAL(30,5));
SELECT SUM(@A2*@B2), SUM(@A2*@B2) / SUM(@C2);
-------------------------------------------------------------------------
DECLARE @A3 Decimal(30,5), @B3 Decimal(30,5), @C3 Decimal(30,5);
SELECT @A3 = 9,
@B3 = 3,
@C3 = 2;
SELECT SUM(@A3*@B3) / SUM(@C3);
-------------------------------------------------------------------------

Probably - but you've no idea what the data type is of A, B and C because the OP hasn't told us. What if they're decimal 18,5? 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 5

This thing might not make more sense :unsure: but yes the values of a,b and c are decimal. I cannot post the values because that is a large data .As you can see i am doing SUM and at the end i am grouping the value based on the product

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 6

Can you tell us the exact specification of the 3 fields/columns? - e.g. A is Decimal(30,5) etc...

Give us some examples too.... Thanx

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 7

Shadab Shah

SSCarpal Tunnel

Points: 4759

September 28, 2012 at 4:04 am

#1543297

A is int, B is int and C is int in database

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 8

If you do the CAST on the first value to be processed, that should fix it:

-------------------------------------------------------------------------

DECLARE @a Int, @b-2 Int, @C Int;

SELECT @a = 9,

@b-2 = 3,

@C = 2;

SELECT Cast(SUM(@A*@B) / SUM(@C) AS DECIMAL(30,5));

SELECT Cast(SUM(@A*@B) AS DECIMAL(30,5))/Cast(SUM(@C) AS DECIMAL(30,5));

-------------------------------------------------------------------------

SELECT SUM(CAST(@A AS DECIMAL(30,5))*@B) / SUM(@C);

-------------------------------------------------------------------------

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Shadab Shah (9/28/2012)
A is int, B is int and C is int in database

Thanks. Here's another way of looking at what Laurie posted;

DECLARE @a Int, @b-2 Int, @C Int;

SELECT @a = 71, @b-2 = 9, @C = 27;

SELECT

@a*@B,

@a*@B/@C, -- variables are all INT, so return value is INT. Decimal fraction is truncated.

CAST(@A*@B/@C AS DECIMAL(30,5)), -- too late, decimal fraction has already been lost

@a*@B/CAST(@C AS DECIMAL(30,5)), -- cast divisor as decimal to return decimal

CAST(@A*@B/CAST(@C AS DECIMAL(30,5)) AS DECIMAL(30,5)) -- recast to desired decimal shape

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 10

laurie-789651 (9/28/2012)
If you do the CAST on the first value to be processed, that should fix it:
-------------------------------------------------------------------------
DECLARE @a Int, @b-2 Int, @C Int;
SELECT @a = 9,
@b-2 = 3,
@C = 2;
SELECT Cast(SUM(@A*@B) / SUM(@C) AS DECIMAL(30,5));
SELECT Cast(SUM(@A*@B) AS DECIMAL(30,5))/Cast(SUM(@C) AS DECIMAL(30,5));
-------------------------------------------------------------------------
SELECT SUM(CAST(@A AS DECIMAL(30,5))*@B) / SUM(@C);
-------------------------------------------------------------------------

Interesting I always thought it had to be the devisor that needed to be cast as a Decimal, but it appears that if either is a decimal then the result will be a decimal.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 11

Jason-299789 (9/28/2012)
laurie-789651 (9/28/2012)
If you do the CAST on the first value to be processed, that should fix it:
-------------------------------------------------------------------------
DECLARE @a Int, @b-2 Int, @C Int;
SELECT @a = 9,
@b-2 = 3,
@C = 2;
SELECT Cast(SUM(@A*@B) / SUM(@C) AS DECIMAL(30,5));
SELECT Cast(SUM(@A*@B) AS DECIMAL(30,5))/Cast(SUM(@C) AS DECIMAL(30,5));
-------------------------------------------------------------------------
SELECT SUM(CAST(@A AS DECIMAL(30,5))*@B) / SUM(@C);
-------------------------------------------------------------------------
Interesting I always thought it had to be the devisor that needed to be cast as a Decimal, but it appears that if either is a decimal then the result will be a decimal.

Strictly, @a doesn't need to be cast because @a*@B will not result in a fraction, but if you cast at the start, the intermediate results will all be decimal.

You can get different numbers of decimal places depending on exactly what you do, so you may wish to play around with it.