SQL Sum function of decimal column returns int instead of decimal
I have a decimal type column 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 must be a decimal number, for example: 13.56
However, the result is always an int, Any suggestions?
source to share
The default scale
is 0. If you want the output in a specific 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 to be stored to the right of the decimal point. This number is subtracted 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. The scale must be a value between 0 and p. cSale can only be specified if precision is specified. Default scale: 0;
source to share