SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Curious Case of SUM Function

By Utsav Verma,

Recently I discovered how SUM could change an output. Consider the following cases. 

Scene 1

What will be output of this query:

CREATE TABLE dbo.CuriousCaseOfSum(val DECIMAL(18,0) NOT NULL)

INSERT INTO dbo.CuriousCaseOfSum(val)
 VALUES (1),(2),(3)

DECLARE @var DECIMAL(12,4)=12.6
SELECT SUM(val)+@var FROM dbo.CuriousCaseOfSum

DROP TABLE dbo.CuriousCaseOfSum

This query will output 19, which is the rounded off value of 1+2+3+12.6. This may not be a big deal as it looks like data type of CuriousCaseOfSum.Val column is decimal 18,0 which might have superseded decimal(12,4), the type of the variable.

Scene 2

OK, let’s increase the precision of the @var variable to the highest possible decimal range, i.e. the all mighty 38 (thunder storms in background).

CREATE TABLE dbo.CuriousCaseOfSum(val DECIMAL(18,0) NOT NULL)

INSERT INTO dbo.CuriousCaseOfSum(val)
 VALUES (1),(2),(3)

DECLARE @var DECIMAL(38,1)=12.5
SELECT @var+SUM(val) FROM dbo.CuriousCaseOfSum

DROP TABLE dbo.CuriousCaseOfSum

The output is still 19!!! Why does our super range fail?

Scene 3

Let’s break values inserted in table into variables.

DECLARE
  @a DECIMAL(18,0)=1,
  @b DECIMAL(18,0)=2,
  @c DECIMAL(18,0)=3,
  @var DECIMAL(36,1)=12.5;

SELECT @a+@b+@c+@var

Let’s run this query and see the output – 18.5.

Scene 4


Enter BOOKS ONLINE. The difference was the use of SUM. Lets see what BOL says about sum.

Return Types

Returns the summation of all expression values in the most precise expression data type.

Expression result

Return type

integer category

int

decimal category (p, s)

decimal(38, s)

money and smallmoney category

money

float and real category

float

If you notice the return type in the case of decimal is always 38 with whatever scale has been mentioned! Let’s connect the dots and solve the mystery. 

In scenes 1 and 2, we were declaring a table with decimal(18,0). However, when we applied SUM over the column, the result changed the data type to decimal(38,0). Since it is decimal (38,0), the value will supersede the variable that was declared as decimal(12,4). In scene 3, since there wasn’t any sum function, data type remained same. That explains why we were getting values with a decimal.

Let's see an even simpler example:

DECLARE @x DECIMAL(38,0)=0

DECLARE @y DECIMAL(24,4)=4.2

SELECT @x-@y

This will output -4. Now let’s change @x type from 38 to 37.

DECLARE @x DECIMAL(37,0)=0
DECLARE @y DECIMAL(24,4)=4.2

SELECT @x-@y

The output is now -4.2!

When the precision is already at 38 and the scale is set to zero, there are no more bytes available for decimal places to be stored in the data type. Hence the result for any addition / subtraction will result in zero decimal places to the right to make sure the precision is accurate. However, when performing multiplication or division, this doesn’t apply because the decimal places and the scale matters. We cannot just truncate the result to fit the datatype of the result.

Conclusion

If you are having any numeric/decimal data type with precision 0, then SUM will return an integer, which can change expected result of query.

 
Total article views: 1830 | Views in the last 30 days: 41
 
Related Articles
FORUM

SSIS Decimal .00 is being dropped in the output file

SSIS Decimal .00 is being dropped in the output file

FORUM

decimal issue

decimal issue

FORUM

Displaying Fewer Decimal Places

Displaying fewer decimal places

FORUM

Rounding Decimal value get wrong result

Rounding Decimal value get wrong result

FORUM

rounding of decimals

rounding of decimals

Tags
aggregate function    
datatype    
decimal    
numeric    
sum()    
t-sql    
 
Contribute