Hi,
I have a problem with displaying numbers after a decimal place in my SQL statement.
For example if I was to calculate 125/511 * 1000 my sql statement would present the answer as 244.00 rather than 244.62.
My sql statement is as follows:
CAST(SUM(Refunds) / SUM(sales) * 1000 AS DECIMAL(9, 2))
Please note that 'Refund' and 'Sale' data also have a decimal (9,2) data format, as I initially thought that data type inconsistency may have been causing the issue.
I welcome your suggestions and thank you for your help.
Loading
VulpesPosted Feb 18, 2013, 7:14 AM
Lynn EmeryPosted Feb 18, 2013, 7:48 AM
Jignesh TrivediPosted Feb 18, 2013, 7:20 AM
hi,
generally, this issue occured while any part of equation is integer.
but as you discrib here, refund and sales both are decimal.
i have try with following code and it work fine for me.
declare @Refunds decimal(9, 2) = 125
declare @sales decimal(9, 2) = 511
select @Refunds/@sales *1000,(CAST(SUM(@Refunds)AS DECIMAL(9, 2)) / CAST(SUM(@sales) AS DECIMAL(9, 2))) * 1000
hope this will help you.