Hi friends, If you are on this page and learning mathematical functions of SQL I believe you have visited my previous post Basics of SQL. If not, do it right now to enhance your knowledge.
There are various mathematical functions available in SQL. Some of these are given below:
This mathematical function always returns a positive value.
e.g. Select ABS(-50) returns 50
This function gives the upper value of the input integer.
e.g. Select Ceiling(14.25) -> returns 15
Select (-14.25) -> returns -14
Floor is mathematical function which gives the nearest lowest value of the supplied input integer.
e.g. Select Floor(14.25) -> returns 14
Select Floor(-14.25) -> returns -15
Power is mathematical function which takes integer input and returns the power of base number.
Syntax : Power(base number, power number)
e.g. Select power(3, 2) -> returns 9
SQRT function returns the square root value of the input integer.
e.g. Select SQRT(81) -> returns 9
SQUARE function returns the square value as expected from its name :p
e.g. Select SQUARE(3) -> returns 9
Rand is a mathematical function which returns a random value but if you will use it in the following way: Select rand(2), then it will always return the same value.
How to generate a random value between 1 to 100?
Use floor function with rand(), this way you will be able to generate a different value each time:
This function will return some random value between 1 and 200.
Or simply use rand()*some_integer_value
The round function is very useful and it rounds the supplied input value to the specified integer.
Syntax: Select Round(number_to_roundoff, int_value_to_specify_round_limit, specify 1 if value needs to be truncated)
e.g. Select round(23.456, 2, 0) – > Rounds the value up to two decimal places: 23.460
Select round(23.456, 2,1) -> Truncate the value up to two decimal places. The output will be 23.450
The round() function also takes negative value to specify the round off/trunc direction to apply. A negative value indicates the truncation/round to apply to the left.
e.g. Select round(230.456, -2) -> rounds the two integer values to the left of decimal value. Thus this function returns 200.00 as an output
Want More on Round function?
Select round(250.456, -2) -> is rounded to 300.00
Select round(250.456, -2, 1) -> value is truncated to the 2 places left to the decimal value and the output will be 200.00
Select round(256.456, -1) -> returns 260.00
Select round(256.456, -1, 1) – > returns 250.00
Hope this clarifies all the confusion about mathematical functions. Want to learn about stored procedures in SQL? Check out my post What are stored procedures and benefits of using Stored procedures.
Till that time c yaa friends 🙂 Comment on this post if you like it because this will keep me going to share good stuff with you. happy learning 🙂