View is rounding down results

Question

View is rounding down results

Eric Carlson-293149

SSC Enthusiast

Points: 149
More actions
August 3, 2006 at 9:46 am

#118901

I have the following view which just multiplies two fields and then divides by 100 to give me my result. Result should be 3.65. However, the view is returning 3 even. I need the decimal places to be returned. I have tried to cast and convert to a decimal without much luck. Is there a way to get my desired result or to make it quit rounding?
SELECT SUM(weight * points) / 100) AS Total
FROM dbo.table
Thanks for any help.

Viewing 7 posts - 1 through 6 (of 6 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

The datatype of the result of an expression is dependant on the data types used in it. Either explicitly CAST() them, or tack on a decimal portion:

SELECT SUM(weight * points) / 100.0) AS Total

or

SELECT SUM(weight * points) / CAST(100 As numeric(10,2))

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

SELECT SUM(weight * points) / 100.00) AS Total

FROM dbo.table

Eric Carlson-293149 SSC Enthusiast Points: 149 More actions · Answer 3

Thanks for the quick reply. While both answers at least get me a decimal, it is rounding to only 3.7. I need it to round to 3.65. I tried both solutions you provided, both with the 3.7 result. My result of the weight * points is 365 and / 100 should give me 3.65. It just appears to still be rounding, only up this time.

Any other ideas?

David.Poole SSC Guru Points: 76000 More actions · Answer 4

SELECT SUM( 
   CAST(weight * points AS DECIMAL(10,2))
   / CAST(100.00 AS DECIMAL(10,2))
)

LinkedIn Profile

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Read topic "converting data types, overview" in BOL.

Probably it will give you same idea how to deal with precisions in SQL.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Get 'em Serqiy!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)