MySQL Numeric Functions

Numeric value functions can take a variety of data types as input, but the output is always a numeric value. 

Function	Description
ABS	Returns the absolute value of a number
ACOS	Returns the arc cosine of a number
ASIN	Returns the arc sine of a number
ATAN	Returns the arc tangent of a number or the arc tangent of n and m
ATAN2	Returns the arc tangent of n and m
AVG	Returns the average value of an expression
CEIL	Returns the smallest integer value that is greater than or equal to a number
CEILING	Returns the smallest integer value that is greater than or equal to a number
COS	Returns the cosine of a number
COT	Returns the cotangent of a number
COUNT	Returns the number of records in a select query
DEGREES	Converts a radian value into degrees
DIV	Used for integer division
EXP	Returns e raised to the power of number
FLOOR	Returns the largest integer value that is less than or equal to a number
GREATEST	Returns the greatest value in a list of expressions
LEAST	Returns the smallest value in a list of expressions
LN	Returns the natural logarithm of a number
LOG	Returns the natural logarithm of a number or the logarithm of a number to a specified base
LOG10	Returns the base-10 logarithm of a number
LOG2	Returns the base-2 logarithm of a number
MAX	Returns the maximum value of an expression
MIN	Returns the minimum value of an expression
MOD	Returns the remainder of n divided by m
PI	Returns the value of PI displayed with 6 decimal places
POW	Returns m raised to the nth power
POWER	Returns m raised to the nth power
RADIANS	Converts a value in degrees to radians
RAND	Returns a random number or a random number within a range
ROUND	Returns a number rounded to a certain number of decimal places
SIGN	Returns a value indicating the sign of a number
SIN	Returns the sine of a number
SQRT	Returns the square root of a number
SUM	Returns the summed value of an expression
TAN	Returns the tangent of a number
TRUNCATE	Returns a number truncated to a certain number of decimal places

Aggregate Functions

When dealing with a set of table rows, often what you want to know is some aggregate property that applies to the whole set. SQL has five such aggregate or set functions: COUNT, AVG, MAX, MIN, and SUM.

COUNT

The COUNT function returns the number of rows in a table, or the number of rows that meet a specified condition.

AVG

The AVG function calculates and returns the average of the values in the specified column. It works only on columns that contain numeric data.

MAX

The MAX function returns the maximum value found in the specified column.

MIN

The MIN function gives you the minimum value found in the specified column.

Numeric Functions

POSITION

POSITION searches for a specified target string within a specified source string and returns the character position where the target string begins.

POSITION (target IN source)

If the function doesn't find the target string, the POSITION function returns a zero value. If the target string has zero length, the POSITION function always returns a value of 1. If any operand in the function has a null value, the result is a null value.

EXTRACT

The EXTRACT function extracts a single field from a datetime or an interval.

CHARACTER_LENGTH

The CHARACTER_LENGTH function returns the number of characters in a character string.

ABS

The ABS function returns the absolute value of a numeric value expression.

TRIGONOMETRIC FUNCTIONS

The trig functions give you the values you would expect, such as the sine of an angle or the hyperbolic tangent.

LOGARITHMIC FUNCTIONS

The logarithmic functions enable you to generate the logarithm of a number, either a base-10 logarithm, a natural logarithm, or a logarithm to a base that you specify.

MOD

The MOD function returns the modulus, the remainder of division of one number by another, of two numeric value expressions.

EXP

This function raises the base of the natural logarithms e to the power specified by a numeric value expression.

POWER

This function raises the value of the first numeric value expression to the power of the second numeric value expression.

SQRT

This function returns the square root of the value of the numeric value expression.

FLOOR

This function rounds the numeric value expression to the largest integer not greater than the expression.

CEIL

This function rounds the numeric value expression to the smallest integer not less than the expression.