none
How to prevent Auto-Rounding in SQL

    Question

  • 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 ?

    Thursday, March 31, 2011 12:52 PM

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
    Thursday, March 31, 2011 1:00 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

    Thursday, March 31, 2011 12:58 PM
  • 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
    Thursday, March 31, 2011 1:00 PM
  • Thanks guys.

     

    @Manfred,

    The reason i'm dividing it by sum(1) is to indicate it as a percetage of the total.

    Thursday, March 31, 2011 1:17 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
    
    
    Thursday, March 31, 2011 1:22 PM