CODEWITHSUNDEEP
sql-serversumdecimal
Emmanuel Santana
Emmanuel Santana

Reputation: 183

SUM function does not return decimal

I need your help:

I want to get the SUM of the the Amount field, it contains decimal values but I get just the INTEGER part and I need the DECIMAL too:

DECLARE @TOTAL AS DECIMAL(13,2)

SET @Total = (SELECT SUM(Amount)
              FROM t_DownPmtTrans
              WHERE MortgageID = @MortgageID 
                AND DatePaid IS NULL 
                AND SchedPayment IS NOT NULL)

I have tried with a CURSOR but I get the same:

OPEN dpt_cursor 
SET @Total= 0.0

FETCH NEXT FROM dpt_cursor INTO @DownPmtTransID, @Amount

WHILE @@FETCH_STATUS= 0 
BEGIN

   PRINT @Amount
   SET @Total = (@Total + @Amount)

   FETCH NEXT FROM dpt_cursor
     INTO @DownPmtTransID, @Amount
END 

RETURN  @Total* (-1)
CLOSE dpt_cursor 
DEALLOCATE dpt_cursor

Thanks!!

Upvotes: 1

Views: 21206

Answers (3)

Terry Downing
Terry Downing

Reputation: 31

You need to set precision and scale rather than size http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.scale(v=vs.100).aspx

var outputParameter = new SqlParameter
{
    Direction = ParameterDirection.Output,
    ParameterName = "your parameter name in your stored procedure",
    SqlDbType = SqlDbType.Decimal,
    Precision = 13,
    Scale = 2
};

Declare an array of SQL parameters and add the parameter to it

var parameters = new SqlParameter[1];
parameters[0] = outputParameter

declare a command object

var command = new SqlCommand (); //don't forget everything else
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "stored procedure name"
command.Parameters.AddRange (parameters);

Execute your command

command.ExecuteNonQuery ();

now read the value from the parameters collection

var value = (decimal) command.Parameters[0].Value;

Upvotes: 3

Eric J. Price
Eric J. Price

Reputation: 2785

I can't replicate either, but in any event converting the column you're summing to the datatype of your variable should get you what you want. The only reason this wouldn't be the case is if "Amount" is an int...

DECLARE @TOTAL AS DECIMAL(13,2)

SET     @Total =   (SELECT  SUM(Convert(DECIMAL(13,2),Amount))
                    FROM    t_DownPmtTrans
                    WHERE   MortgageID = @MortgageID 
                    AND     DatePaid IS NULL 
                    AND     SchedPayment IS NOT NULL)

Since the issue is outside of SQL I did a quick search and found this: http://forums.asp.net/t/33723.aspx/1

According to it this is a known issue. I looked around a little more and while this guy didn't say it helped I would suggest creating a SqlParameter object so you can set the precision property of the object and then pass that in your Add method.

Upvotes: 1

gmail user
gmail user

Reputation: 2783

As per MSDN, if the expression is decimal, sum will return decimal(38, s). Make sure the datatype is of amount is decimal. Or try to convert it to decimal using cast or convert. This options may help you.

Upvotes: 2

Related Questions