user1902849 user1902849 - 1 month ago 9
SQL Question

SQL SERVER 2014 SUM Function doesn't return correct value

I've a data as below, when I total Wage_Value it should give me 0 value

enter image description here

However SQL SERVER

SUM
function doesn't return correct value (see result below).

The data type of
Wage_Value
is decimal(18,2).

select [Employee Code], Wage_Type, sum(Wage_Value) 'Total_Wage_Value'
from [SSO_PAYROLL_20151231]
where [Employee Code] = '3870299'
group by [Employee Code], Wage_Type


enter image description here

Answer

I don't believe you. I think the type of Wage_Value is a floating point number, not a decimal. SQL Server is quite explicit about the output type of a sum(). For a decimal input, the output is decimal. See the documentation.

Also, because the values are sometimes output with one decimal place and sometimes with 2 ("852.9" and "-317.07"), I think the representation is a floating point number.

This is easy enough to check. Do an explicit conversion:

select [Employee Code], Wage_Type,
       sum(cast(Wage_Value as decimal(18, 2)) as Total_Wage_Value
from [SSO_PAYROLL_20151231]
where [Employee Code] = '3870299'
group by [Employee Code], Wage_Type