CODEWITHSUNDEEP
sqlsql-servert-sql
Alaa Alweish
Alaa Alweish

Reputation: 9084

SQL Sum function of a decimal column returns int instead of decimal

I have a column of decimal type and i need to use the sum function on it like this:

   declare @credit decimal = (select
                               (    select ISNULL(SUM(Convert(DECIMAL(13,2), Amount)),0)
                                    from TransactionDetail as t1
                                    where t1.AccountFrom = @fromAccount and t1.AccountTo = @toAccount
                               ) -     
                               (    select ISNULL(SUM(Convert(DECIMAL(13,2),Amount)),0)
                                    from TransactionDetail as t1
                                    where t1.AccountFrom = @toAccount  and t1.AccountTo = @fromAccount
                               ) 
                           )
select @credit

The output should be a decimal number like : 13.56

However, the result is always int, Any suggestions?

Upvotes: 3

Views: 4105

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

The default scale is 0. If you want the result as a particular format, try explicitly adding precision and scale to the variable:

declare @credit decimal(13, 2) = (select . . .

This behavior is well documented:

The number of decimal digits that will be stored to the right of the decimal point. This number is substracted from p to determine the maximum number of digits to the left of the decimal point. The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. cSale can be specified only if precision is specified. The default scale is 0;

Upvotes: 5

Related Questions