Mathematical Functions

ABS - function returns the absolute value of a number.
SELECT abs(-23) FROM DUAL --would return 23
SELECT abs(-23.6) FROM DUAL --would return 23.6
SELECT abs(-23.65) FROM DUAL --would return 23.65
SELECT abs(23.65) FROM DUAL --would return 23.65
SELECT abs(23.65 * -1) FROM DUAL --would return 23.65


ACOS - function returns the arc cosine of a number.
SELECT acos(1) FROM DUAL -- would return 0
SELECT acos(0) FROM DUAL -- would return 1.5707963267949
SELECT acos(-1) FROM DUAL -- would return 3.14159265358979

asin function returns the arc sine of a number.
SELECT asin(0.2) FROM DUAL -- would return 0.201357920790331
SELECT asin(0.35) FROM DUAL -- would return 0.35757110364551
SELECT asin(-0.15) FROM DUAL -- would return -0.150568272776686

atan function returns the arc tangent of a number.
SELECT atan(0.2) FROM DUAL -- would return 0.197395559849881
SELECT atan(0.35) FROM DUAL -- would return 0.336674819386727
SELECT atan(-0.15) FROM DUAL --would return -0.148889947609497


atan2 function returns the arc tangent of n and m.
SELECT atan2(0.2, 0.3) FROM DUAL -- would return 0.588002603547568
SELECT atan2(0.35, -0.15) FROM DUAL --would return 1.97568811307998
SELECT atan2(-0.3, -0.4) FROM DUAL --would return -2.49809154479651


Avg function returns the average value of an expression.
SELECT Avg(sal) as "Avg Salary" FROM emp WHERE sal > 25000;
SELECT Avg(DISTINCT sal) as "Avg Salary" FROM emp WHERE sal > 25000;

BIT_TO_NUM - function converts a bit vector to a number.
SELECT bin_to_num(1) FROM DUAL -- would return 1
SELECT bin_to_num(1,0) FROM DUAL --would return 2
SELECT bin_to_num(1,1) FROM DUAL --would return 3
SELECT bin_to_num(1,1,1,0) FROM DUAL --would return 14
SELECT bin_to_num(1,1,1,1) FROM DUAL --would return 15

CEIL - ceil function returns the smallest integer value that is greater than or equal to a number.

SELECT ceil(32.02) FROM DUAL --would return -33
SELECT ceil(32.65) FROM DUAL --would return 33.
SELECT ceil(32) FROM DUAL --would return 32.
SELECT ceil(-32.65) FROM DUAL --would return -32.
SELECT ceil(-32) FROM DUAL --would return -32


floor function returns the largest integer value that is equal to or less than a number.

SELECT floor(5.9) FROM DUAL -- would return 5
SELECT floor(34.29) FROM DUAL -- would return 34
SELECT floor(-5.9) FROM DUAL --would return -6



cos - function returns the cosine of a number.
SELECT cos(0.2) FROM DUAL -- would return 0.980066577841242
SELECT cos(0.35) FROM DUAL -- would return 0.939372712847379
SELECT cos(-3.15) FROM DUAL -- would return -0.999964658471342
SELECT cos(200) FROM DUAL -- would return 0.487187675007006

cosh -function returns the hyperbolic cosine of a number.
select cosh(0.2) from dual -- would return 1.02006675561908
select cosh(0.35) from dual -- would return 1.06187781915599
select cosh(-3.15) from dual -- would return 11.6894583539049
select cosh(200) from dual -- would return 3.61298688406287E86

cosh - function returns the hyperbolic cosine of a number.
select cosh(0.2) from dual -- would return 1.02006675561908
select cosh(0.35) from dual -- would return 1.06187781915599
select cosh(-3.15) from dual -- would return 11.6894583539049
select cosh(200) from dual -- would return 3.61298688406287E86


COUNT - function will only count those records in which the field in the brackets is NOT NULL

SELECT COUNT(*) as "Number of employees" FROM emp
SELECT COUNT(distinct dept) FROM emp

extract
select extract(YEAR FROM DATE '2003-08-22') from dual -- would return 2003
select extract(MONTH FROM DATE '2003-08-22') from dual -- would return 8
select extract(DAY FROM DATE '2003-08-22') from dual --would return 22


greatest function returns the greatest value in a list of expressions.

SELECT greatest(2, 5, 12, 3) FROM DUAL -- would return 12
SELECT greatest('2', '5', '12', '3') FROM DUAL -- would return '5'
SELECT greatest('apples', 'oranges', 'bananas') FROM DUAL --would return 'oranges'
SELECT greatest('apples', 'applis', 'applas') FROM DUAL --would return 'applis'

least function returns the smallest value in a list of expressions.

SELECT least(2, 5, 12, 3) FROM DUAL -- would return 2
SELECT least('2', '5', '12', '3') FROM DUAL --would return '12'
SELECT least('apples', 'oranges', 'bananas') FROM DUAL -- would return 'apples'
SELECT least('apples', 'applis', 'applas') FROM DUAL --would return 'applas'
SELECT least('apples', 'applis', 'applas', null) FROM DUAL -- would return NULL




LOG function returns the logarithm of n base m.
SELECT log(10, 20) FROM DUAL -- would return 1.30102999566398
SELECT log(2, 15) FROM DUAL -- would return 3.90689059560852
SELECT log(100, 1) FROM DUAL --would return 0


MAX function returns the maximum value of an expression.

SELECT MAX(SAL) FROM EMP


MIN function returns the minimum value of an expression.

SELECT MIN(salary) as "Lowest salary" FROM employees;


median function returns the median of an expression.

select median(salary) from employees where department = 'Marketing';


mod function returns the remainder of m divided by n.

SELECT mod(15, 4) FROM DUAL -- would return 3
SELECT mod(15, 0) FROM DUAL -- would return 15
SELECT mod(11.6, 2) FROM DUAL -- would return 1.6
SELECT mod(11.6, 2.1) FROM DUAL -- would return 1.1
SELECT mod(-15, 4) FROM DUAL -- would return -3
SELECT mod(-15, 0) FROM DUAL -- would return -15


power function returns m raised to the nth power.

select power(3, 2) from dual -- would return 9
select power(5, 3) from dual --would return 125
select power(-5, 3) from dual -- would return -125
select power(6.2, 3) from dual -- would return 238.328
select power(6.2, 3.5) from dual -- would return 593.431934277892



rank function returns the rank of a value in a group of values (view must create )

select customerid, ordertotalamount,
rank() OVER (PARTITION BY saleschannelid ORDER BY ordertotalamount) rank
from posheaders


remainder function returns the remainder of m divided by n.

select remainder(15, 6) from dual -- would return 3
select remainder(15, 5) from dual -- would return 0
select remainder(15, 4) from dual -- would return -1
select remainder(11.6, 2) from dual -- would return -0.4
select remainder(11.6, 2.1) from dual -- would return -1
select remainder(-15, 4) from dual -- would return 1


round function returns a number rounded to a certain number of decimal places.
select round(125.315) from dual -- would return 125
select round(125.315, 0) from dual -- would return 125
select round(125.315, 1) from dual -- would return 125.3
select round(125.315, 2) from dual -- would return 125.32
select round(125.315, 3) from dual -- would return 125.315
select round(-125.315, 2) from dual -- would return -125.32

select round(to_date ('22-AUG-03'),'YEAR') from dual -- would return '01-JAN-04'
select round(to_date ('22-AUG-03'),'Q') from dual --would return '01-OCT-03'
select round(to_date ('22-AUG-03'),'MONTH') from dual -- would return '01-SEP-03'
select round(to_date ('22-AUG-03'),'DDD') from dual -- would return '22-AUG-03'
select round(to_date ('22-AUG-03'),'DAY') from dual -- would return '24-AUG-03'


sin function returns the sine of n.

select sin(3) from dual -- would return 0.141120008059867
select sin(5.2) from dual -- would return -0.883454655720153
select sin(-5.2) from dual -- would return 0.883454655720153


sqrt function returns the square root of n.
select sqrt(9) from dual -- would return 3
select sqrt(37) from dual -- would return 6.08276253029822
select sqrt(5.617) from dual -- would return 2.37002109695251

SUM function returns the summed value of an expression.

SELECT SUM(salary) as "Total Salary" FROM employees WHERE salary > 25000;

tan function returns the tangent of n.
select tan(3) from dual -- would return -0.142546543074278
select tan(5.2) from dual -- would return -1.88564187751976
select tan(-5.2) from dual -- would return 1.88564187751976


trunc function returns a number truncated to a certain number of decimal places.

select trunc(125.815) from dual -- would return 125
select trunc(125.815, 0) from dual -- would return 125
select trunc(125.815, 1) from dual -- would return 125.8
select trunc(125.815, 2) from daul -- would return 125.81
select trunc(125.815, 3) from daul -- would return 125.815
select trunc(-125.815, 2) from daul -- would return -125.81
select trunc(125.815, -1) from dual -- would return 120
select trunc(125.815, -2) from dual -- would return 100
select trunc(125.815, -3) from dual -- would return 0


select trunc(to_date('22-AUG-03'), 'YEAR') from dual -- would return '01-JAN-03'
select trunc(to_date('22-AUG-03'), 'Q') from dual -- would return '01-JUL-03'
select trunc(to_date('22-AUG-03'), 'MONTH') from dual -- would return '01-AUG-03'
select trunc(to_date('22-AUG-03'), 'DDD') from dual -- would return '22-AUG-03'
select trunc(to_date('22-AUG-03'), 'DAY') from dual -- would return '17-AUG-03'