Oracle Numeric Functions
 
    Version 10.1

 
 
ABS
Returns the absolute value of a number     ABS(<value>)
SELECT ABS(-100) FROM dual;
 
ACOS
Returns the arc cosine of a number     ACOS(<value>)
SELECT ACOS(0.5) ARC_COSINE
FROM dual;
 
ASIN
Returns the arc sin of a number     ASIN(<value>)
SELECT ASIN(0.5) ARC_SINE
FROM dual;
 
ATAN
Returns the arc tanget of a number     ATAN(<value>)
SELECT ATAN(0.5) ARC_TANGENT
FROM dual;
 
ATAN2
Arc tanget of the first value divided by the arc tangent of the second     ATAN2(<value>, <value>)
SELECT ATAN2(0.5, 0.4) ARC_TANGET_DIV
FROM dual;
 
AVG
Returns the average of a column of numbers     AVG(<value>)
SELECT AVG(initial_extent) FROM user_tables;

SELECT AVG(DISTINCT initial_extent) FROM user_tables;
 
BIN_TO_NUM
Converts a bit vector to a number     BIN_TO_NUM(<value>,<value>,....)
SELECT BIN_TO_NUM(1, 0, 1, 0) FROM dual;
 
CEIL
Smallest integer greater than or equal to a decimal value     CEIL(<value>)
SELECT CEIL(12345.67)
FROM dual;
 
COALESCE
Returns the first non-null value     COALESCE(<value>, <value>, ....)
CREATE TABLE test (
col1  NUMBER(3),
col2  NUMBER(3),
col3  NUMBER(3));

INSERT INTO test VALUES (1, NULL, NULL);
INSERT INTO test VALUES (NULL, 2, NULL);
INSERT INTO test VALUES (NULL, NULL, 3);
INSERT INTO test VALUES (1, NULL, 3);
INSERT INTO test VALUES (NULL, 2, 3);

SELECT * FROM test;

SELECT COALESCE(col1, col2, col3) FROM test;
 
CORR
Returns the coefficient of correlation of a set of number pairs    

For information go to http://tahiti.oracle.com
 
CORR_K
Calculates the Pearson's correlation coefficient    

For information go to http://tahiti.oracle.com
 
CORR_S
Calculates the Spearman's rho correlation coefficient    

For information go to http://tahiti.oracle.com
 
COS
Returns the cosine of a number (an angle expressed in radians)     COS(<value>)
SELECT COS(180*3.1415926/180) COSINE
FROM dual;
 
COSH
The hyperbolic cosine of a number     COSH(<value>)
SELECT COSH(0) HYPERBOLIC_COS FROM dual;
 
COUNT
The number of rows returned by a query     COUNT(<value>)
SELECT COUNT(*) FROM all_objects;
 
COVAR_POP
The population covariance of a set of number pairs     COVAR_POP(<expression1>, <expression2>)
conn sh/sh

SELECT t.calendar_month_number,
COVAR_POP(s.amount_sold, s.amount_sold) AS CP,
COVAR_SAMP(s.amount_sold, s.amount_sold) AS CS
FROM sales s, times t
WHERE s.time_id = t.time_id
AND t.calendar_year = 1998
GROUP BY t.calendar_month_number;
 
COVAR_SAMP
The sample  covariance of a set of number pairs     COVAR_POP(<expression1>, <expression2>)
See COVAR_POP demo.
 
CUME_DIST
Returns the cumulative distribution of a value in a group of values     CUME_DIST(<value>)
conn oe/oe

SELECT CUME_DIST(15500, .05) WITHIN GROUP
(ORDER BY salary, commission_pct) CUME_DIST_OF_15500
FROM employees;
 
DENSE_RANK
Computes the rank of a row in an ordered group of rows     DENSE_RANK(<value>)
conn oe/oe

SELECT DENSE_RANK(15500, .05) WITHIN GROUP
(ORDER BY salary DESC, commission_pct) DENSE_RANK_OF_15500
FROM employees;
 
EXP
Returns e raised to to an exponential power     EXP(<value>)
SELECT 2.71828183 * 2.71828183 FROM dual;

SELECT EXP(2) FROM dual;

SELECT 2.71828183 * 2.71828183 * 2.71828183 FROM dual;

SELECT EXP(3) FROM dual;
 
FIRST
Returns the row ranked first using DENSE_RANK     conn oe/oe

SELECT department_id,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) WORST,
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
BEST
FROM employees
GROUP BY department_id;
 
FLOOR
Returns the largest integer less than or equal to a decimal value     FLOOR(<string_or_column>)
SELECT FLOOR(12345.67) FROM dual;
 
GREATEST
Returns the largest of multiple values     GREATEST(<value>, <value>, .... )
SELECT GREATEST(9, 67.6, 10) FROM dual;
 
LAST
Returns the row ranked last using DENSE_RANK    

See FIRST demo
 
LEAST
Returns the smallst of multiple values     LEAST(<value>, <value>, ....)
SELECT LEAST(9, 67.6, 10) FROM dual;
 
LN
Returns the natural log of a number     LN(<value>)
SELECT LN(2) NATURAL_LOG FROM dual;
 
LOG
Returns the logarithm, base m of n     LOG(<m_value>,<n_value>)
SELECT LOG(10,100) FROM dual;

SELECT LOG(100,10) FROM dual;
 
MAX
Returns the maximum value returned by a query     MAX(<column_name>)
SELECT MAX(initial_extent) FROM all_tables;
 
MEDIAN
Returns the middle value of a set     MEDIAN(<column_name>)
SELECT MEDIAN(initial_extent) FROM all_tables;
 
MIN
Returns the minimum value returned by a query     MIN(<column_name>)
SELECT MIN(initial_extent) FROM all_tables;
 
MOD
Returns the modulus of a number. Same as remainder except uses FLOOR     MOD(<m_value>, <n_value>)
SELECT MOD(3, 2) FROM dual;

SELECT MOD(6, 2) FROM dual;
 
NANVL
Returns Alternate Number If The Value Is Not A Number     NANVL(<value_evaluated>, <value_returned>)
CREATE TABLE fpd (
dec_num    NUMBER(10,2),
bin_double BINARY_DOUBLE,
bin_float  BINARY_FLOAT);

INSERT INTO fpd VALUES (0, 'NaN', 'NaN');
COMMIT;

SELECT * FROM fpd;

SELECT bin_double, NANVL(bin_double, 0)
FROM fpd;

SELECT bin_float, NANVL(bin_float, 0)
FROM fpd;

INSERT INTO fpd VALUES ('NaN', 'NaN', 'NaN');
COMMIT;

SELECT bin_float, NANVL(dec_number, 0)
FROM fpd;
 
NVL
Returns a Value if the Expression IS NULL     NVL(<expression>, <return_value>)
set serveroutput on

DECLARE
 i PLS_INTEGER;
BEGIN
  SELECT NVL(i, 93)
  INTO i
  FROM dual;

  dbms_output.put_line('i1: ' || i);

  SELECT NVL(i, 39)
  INTO i
  FROM dual;

  dbms_output.put_line('i2: ' || i);
END;
/
 
NVL2
Returns First Value if NULL, Second Value if NOT NULL.     NVL2(<expression>, <return_if_value>, <return_if_not_null>)
CREATE TABLE ats (
category  VARCHAR2(20),
outval    NUMBER(3),
inval     NUMBER(3));

INSERT INTO ats VALUES ('Groceries', 10, NULL);
INSERT INTO ats VALUES ('Payroll', NULL, 100);
INSERT INTO ats VALUES ('Groceries', 20, NULL);
INSERT INTO ats VALUES ('Payroll', NULL, 200);
INSERT INTO ats VALUES ('Groceries', 30, NULL);

SELECT * FROM ats;

SELECT category, SUM(NVL2(outval, -outval, inval)) NET
FROM ats
GROUP BY category;
 
PERCENT_RANK
Calculates for a row r and a sort specification, the rank of row r minus 1 divided by the number of rows in the aggregate group     PERCENT_RANK(<expression>) WITHIN GROUP
(ORDER BY <expression> <ASC | DESC> NULLS <FIRST | LAST>)
conn oe/oe

SELECT PERCENT_RANK(15000, .05) WITHIN GROUP
(ORDER BY salary, commission_pct) "Percent-Rank"
FROM employees;
 
PERCENTILE_CONT
Takes a percentile value and a sort specification, and returns an interpolated value that would fall into that percentile value with respect to the sort specification     PERCENTILE_CONT(<expression>) WITHIN GROUP
(ORDER BY <expression> <ASC | DESC> NULLS <FIRST | LAST>)
OVER (<query_partition_clause>);
conn oe/oe

SELECT department_id, PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY salary DESC) MEDIAN_CONT
FROM employees
GROUP BY department_id;
 
PERCENTILE_DISC
Takes a percentile value and a sort specification and returns an element from the set     PERCENTILE_DISC(<expression>) WITHIN GROUP
(ORDER BY <expression> <ASC | DESC> NULLS <FIRST | LAST>)
OVER (<query_partition_clause>);
conn oe/oe

SELECT department_id, PERCENTILE_DISC(0.5)
WITHIN GROUP (ORDER BY salary DESC) MEDIAN_DISC
FROM employees
GROUP BY department_id;
 
POWER
Returns m_value raised to the n_value power     POWER(<m_value>, <n_value>)
SELECT 2*2*2 FROM dual;

SELECT POWER(2,3) FROM dual;
 
RANK
Calculates the rank of a value in a group of values     RANK(<column_name>) WITHIN GROUP
SELECT RANK(15500, .05) WITHIN GROUP
(ORDER BY salary, commission_pct) SAL_RANK
FROM employees;
 
REGR_ (Linear Regression) Functions
REGR_AVGX    

For more information go to http://tahiti.oracle.com

SELECT s.channel_id,
REGR_SLOPE(s.quantity_sold, p.prod_list_price) SLOPE,
REGR_INTERCEPT(s.quantity_sold, p.prod_list_price) INTCPT,
REGR_R2(s.quantity_sold, p.prod_list_price) RSQR,
REGR_COUNT(s.quantity_sold, p.prod_list_price) COUNT,
REGR_AVGX(s.quantity_sold, p.prod_list_price) AVGLISTP,
REGR_AVGY(s.quantity_sold, p.prod_list_price) AVGQSOLD
FROM sales s, products p
WHERE s.prod_id=p.prod_id
AND p.prod_category='Women'
AND s.time_id=to_DATE('10-OCT-2000')
GROUP BY s.channel_id;
REGR_AVGY
REGR_COUNT
REGR_INTERCEPT
REGR_R2
REGR_SLOPE
REGR_SXX
REGR_SXY
REGR_SYY
 
REMAINDER
Returns the modulus of a number (the remainder from dividing m by n.

Same as mod except uses ROUND     REMAINDER(<m_value>, <n_value>)
SELECT REMAINDER(2,3) FROM dual;
 
REVERSE
Reverses the bytes from which a number has been created     REVERSE(<value>)
SELECT 123 FROM dual;

SELECT DUMP(123) FROM dual;

SELECT REVERSE(123) FROM dual;

SELECT DUMP(REVERSE(123)) FROM dual;
 
ROUND
Returns a value rounded to integer places     ROUND(<value>, <integer>)
SELECT ROUND(3.1415926, 4) FROM dual;
 
SIGN
Returns the sign of a number     SIGN(<value>)
SELECT SIGN(15) FROM dual;

SELECT SIGN(-5) FROM dual;
 
SIN
Returns the sine of a number     SIN(<value>)
SELECT SIN(2) SINE FROM dual;
 
SINH
Returns the hyperbolic sine of a number     SINH(<value>)
SELECT SINH(2) HYPERBOLIC_SINE FROM dual;
 
SQRT
Returns the square root of a number     SQRT(<value>)
SELECT SQRT(2) FROM dual;
 
STATS_ (Statistical) Functions
STATS_BINOMIAL_TEST    

For information go to http://tahiti.oracle.com
STATS_CROSSTAB
STATS_F_TEST
STATS_KS_TEST
STATS_MW_TEST
STATS_ONE_WAY_ANOVA
STATS_T_TEST
STATS_WSR_TEST
STDDEV_POP
STDDEV_SAMP
 
STATS_MODE
Returns the value that occurs with the greatest frequency     STATS_MODE(<expression>)
conn oe/oe

SELECT department_id, STATS_MODE(salary)
FROM employees
GROUP BY department_id;

SELECT salary, COUNT(*)
FROM employees
WHERE department_id = 50
GROUP BY salary;
 
STDDEV
Sample standard deviation of an expression     STDDEV(<expression>)
conn oe/oe

SELECT STDDEV(salary) DEVIATION
FROM employees;
 
SUM
Computes the sum of an expression     SUM(<column_name>)
SELECT SUM(initial_extent) FROM all_tables;

SELECT SUM(DISTINCT initial_extent) FROM all_tables;
 
TAN
Tangent in radians     TAN(<value>)
SELECT TAN(135 * 3.14159265359/180) FROM dual;
 
TANH
Hyperbolic tangent     TANH(<value>)
SELECT TANH(135 * 3.14159265359/180) FROM dual;
 
TO_BINARY_DOUBLE
Converts a Value to the BINARY_DOUBLE Data Type     TO_BINARY_DOUBLE(<value>);
See TO_NUMBER demo.
 
TO_BINARY_FLOAT
Converts a Value to the BINARY_FLOAT Data Type     TO_BINARY_FLOAT(<value>);
See TO_NUMBER demo.
 
TO_NUMBER
Converts a string to the NUMBER data type     TO_NUMBER(<value>[, <format>, <NLS parameter>]);
CREATE TABLE test (
testcol VARCHAR2(10));

INSERT INTO test VALUES ('12345.67');

SELECT TO_BINARY_DOUBLE(testcol) BIN_DOUBLE, TO_BINARY_FLOAT(testcol) BIN_FLOAT, TO_NUMBER(testcol) NMBR
FROM test;
Converts a HEX number into a FLOAT     TO_NUMBER(<value>, <format>);
SELECT TO_NUMBER('0A', 'XX')
FROM dual;
 
TRUNC
Truncates a Number to the Specified Number of Decimal Places     TRUNC(<value>)
SELECT TRUNC(15.79, 1) FROM dual;

SELECT TRUNC(15.79, -1) FROM dual;
 
VAR_POP
Population  Variance of a Set of Numbers     VAR_POP(<column_name>)
SELECT VAR_POP(data_length) FROM all_tab_columns;
 
VAR_SAMP
Sample Variance of a Set of Numbers     VAR_SAMP(<column_name>)
SELECT VAR_SAMP(data_length) FROM all_tab_columns;
 
VARIANCE
Variance of an Expression     VARIANCE(<value>)
SELECT VARIANCE(initial_extent) FROM user_tables;
 
VSIZE
Byte Size     VSIZE(<value>)
SELECT VSIZE(initial_extent) FROM all_tables;
 
WIDTH_BUCKET
Construct Equiwidth Histograms

n+1 bucket is for overflow     WIDTH_BUCKET(<value>, <min_value>, <max_value>, <number_of_buckets>);
conn oe/oe

SELECT customer_id, cust_last_name, credit_limit,
WIDTH_BUCKET(credit_limit, 100, 4000, 10) CREDIT_GRP
FROM customers
WHERE nls_territory = 'SWITZERLAND'
ORDER BY credit_grp;