Using SQL command to sum decimal values

[OliverR]

Hi guys,

I'm trying to get a field to display the SUM of a column in a session, but running into problems. The problem being that the values stored in the column are DECIMAL values (they're weights of boxes). I tried to convert the values to decimal using the following code:

SELECT SUM(CONVERT(decimal(4,2), Field8)) FROM Session1 WHERE Field1='[*0*]'​

But that's not working, it just gives me a result of '0'. I also tried using CONVERT(float, Field8), but that gives me the same result (0).

It does work if I only store integer values in the column and change my SQL query to:

SELECT SUM(CONVERT(int, Field8)) FROM Session1 WHERE Field1='[*0*]'
​

So my question is: what SQL command do I have to use to be able to use the SUM() function on a column that contains decimal values?

 

[Fahim Ali]

Hi Oliver,

The following should be the correct expression to SUM decimal values:

SELECT CAST(SUM(CONVERT(FLOAT, Field8)) AS NVARCHAR(255)) FROM Session1 WHERE Field1='[*0*]';​

 

[OliverR]

Yep, that seems to work. Thanks a bunch!