Answered by:
How to prevent AutoRounding in SQL

I have a query that goes like:
Select
T1
.ID,100
*SUM (CASE WHEN THRESHOLD <= 1 THEN 1 ELSE 0 END)/(SUM(1)) as '1',100
*SUM (CASE WHEN THRESHOLD > 1 and LOGICAL_CHANNEL_UTILISATION <= 2 THEN 1 ELSE 0 END)/(SUM(1)) as '2',100
*SUM (CASE WHEN THRESHOLD > 2 and LOGICAL_CHANNEL_UTILISATION <= 3 THEN 1 ELSE 0 END)/(SUM(1))as '3'from T1
GROUP BY T1
I find that the output (1,2,3) is automatically rounded off to zero decimal places. Is there a way to prevent this ?
Question
Answers

You did an integer division  therefore you got integer results (with no decimal places).
Select T1.ID, 100. * SUM(CASE WHEN THRESHOLD <= 1 THEN 1 ELSE 0 END)/(SUM(1)) as '1', 100. *SUM (CASE WHEN THRESHOLD > 1 and LOGICAL_CHANNEL_UTILISATION <= 2 THEN 1 ELSE 0 END)/(SUM(1)) as '2', 100.*SUM (CASE WHEN THRESHOLD > 2 and LOGICAL_CHANNEL_UTILISATION <= 3 THEN 1 ELSE 0 END)/(SUM(1))as '3' from T1 GROUP BY T1
If you put a dot (.) after a number value, you declare it to be float  and all the calculations will be float.
I hope you know why you divide by "sum(1)" ... I don't.
 Marked as answer by dct374 Thursday, March 31, 2011 1:16 PM
All replies

Try below query
Select
T1.ID, convert(Numeric(15,2),SUM(CASE WHEN THRESHOLD <= 1 THEN 1 ELSE 0 END))/(SUM(1))*100.0 as '1',
convert
(numeric(15,2),SUM (CASE WHEN THRESHOLD > 1 and LOGICAL_CHANNEL_UTILISATION <= 2 THEN 1 ELSE 0 END))/(SUM(1))*100.0 as '2',
convert
(numeric(15,2),SUM (CASE WHEN THRESHOLD > 2 and LOGICAL_CHANNEL_UTILISATION <= 3 THEN 1 ELSE 0 END))/(SUM(1))*100.0 as '3'
from
T1
GROUP
BY T1

You did an integer division  therefore you got integer results (with no decimal places).
Select T1.ID, 100. * SUM(CASE WHEN THRESHOLD <= 1 THEN 1 ELSE 0 END)/(SUM(1)) as '1', 100. *SUM (CASE WHEN THRESHOLD > 1 and LOGICAL_CHANNEL_UTILISATION <= 2 THEN 1 ELSE 0 END)/(SUM(1)) as '2', 100.*SUM (CASE WHEN THRESHOLD > 2 and LOGICAL_CHANNEL_UTILISATION <= 3 THEN 1 ELSE 0 END)/(SUM(1))as '3' from T1 GROUP BY T1
If you put a dot (.) after a number value, you declare it to be float  and all the calculations will be float.
I hope you know why you divide by "sum(1)" ... I don't.
 Marked as answer by dct374 Thursday, March 31, 2011 1:16 PM


SELECT ID, SUM(CASE WHEN THRESHOLD <= 1 THEN 100E ELSE 0E END) / COUNT(*) AS [1], SUM(CASE WHEN THRESHOLD > 1 AND LOGICAL_CHANNEL_UTILISATION <= 2 THEN 100E ELSE 0E END) / COUNT(*) AS [2], SUM(CASE WHEN THRESHOLD > 2 and LOGICAL_CHANNEL_UTILISATION <= 3 THEN 100E ELSE 0E END) / COUNT(*) AS [3] FROM dbo.Table1 GROUP BY ID
 Proposed as answer by Naomi NModerator Thursday, March 31, 2011 1:59 PM