Thursday, November 22

SQL- Mathematical Functions



Commonly used Mathematical functions are CEILING, FLOOR, ROUND


  • Ceiling( )
          It rounds up to the smallest integer greater than or equal to the number.

          Example:
          select ceiling(363699.2525)    --value 363700


When applied to column the syntax is as follows:

          Syntax - select ceiling (column_name) from Table_name



  • Floor( )
          It is just opposite of ceiling( ), it doesn't round up. It ignores the numbers after decimal   
          point.

          Example:
          select floor(363699.2525)    --value 363699


When applied to column the syntax is as follows:

          Syntax - select floor (column_name) from Table_name


  • Round( )
          The ROUND() function is used to round a numeric field to the number of decimals 
          specified.

          Example: Try each query
          select round(363699.2525,1)   --value 363699.3000

          select round(363699.2525,2)   --value 363699.2500
          select round(363699.2525,3)   --value 363699.2530
          select round(363699.2525,4)   --value 363699.2525


When applied to column the syntax is as follows:

          Syntax - select round (column_name, decimals) from Table_name   

**Decimals specifies the number of decimals to be returned.