SQL Server drops precision when using SUM

Geoff Millikan
Geoff Millikan used Ask the Experts™
on
I want to use SUM() but not lose the significant places...  How do I do that?
//Returns 0.00000117580026124
SELECT 0.66896 /568940.16956

//Returns 0.000001
SELECT SUM(0.66896) / SUM(568940.16956)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
please cast the value as decimal with proper precision..

SELECT SUM(cast(0.66896 as decimal(38,22))) / SUM(568940.16956)
will do it for you.. it gives you 0.00000117580026124..

the reason for this is:

SUM() gives the summation of all expression values in the most precise expression data type.
http://msdn.microsoft.com/en-us/library/ms187810.aspx

Author

Commented:
Weird that the denominator doesn't have to be CAST.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial