SQL Numeric Functions

Review all the SQL numeric functions, including their definitions and examples.

SQL’s numeric functions perform operations on numbers. They take one or more numbers as arguments and return a number as output. Note that numbers may be of different data types, including integers, doubles, and floats. You can learn more about numeric and decimal data types here.

SQL numeric functions can be divided into scalar and aggregate functions. Scalar functions compute a result for each input row individually. For example, applying the ABS() function to a column produces the absolute value for each row within that column. On the other hand, aggregate functions operate on values across multiple rows to produce an output – e.g. applying the MAX() function to a column yields the highest value among all rows within that column.

Check out our course on Standard SQL Functions which includes exercises on these types of functions:

• Numeric
• Text
• Date and time
• Aggregate

Complete the exercises to review and consolidate your knowledge, then put it to a test by taking the final quiz.

Scalar SQL Numeric Functions

SQL scalar numeric functions manipulate individual values – much like a calculator.

Function	Description	Further reading
+	→ Takes two numbers. ← Adds the numbers and returns the result.
-	→ Takes two numbers. ← Subtracts the numbers and returns the result.	* How to subtract values in SQL
*	→ Takes two numbers. ← Multiplies the numbers and returns the result.	* How to multiply values in SQL
/	→ Takes two numbers. ← Divides the numbers and returns the result.	* How the division operator works in SQL * How to handle division by zero in SQL * How to divide columns in MySQL or Oracle * How to divide columns in SQL Server, PostgreSQL, or SQLite
ABS()	→ Takes one number. ← Returns the absolute value of the number.	* How to compute an absolute value in SQL
ACOS()	→ Takes a number between -1 and 1. ← Returns the arc cosine value in radians.
ASIN()	→ Takes a number between -1 and 1. ← Returns the arc sine value in radians.
ATAN()	→ Takes one number. ← Returns the arc tangent value in radians.
CEILING()	→ Takes one number. ← Returns the smallest integer value that is greater than or equal to the number.	* How to round up a number to the nearest integer in SQL
COS()	→ Takes one number. ← Returns the cosine value in radians.
COT()	→ Takes one number. ← Returns the cotangent value in radians.
DEGREES()	→ Takes a number in radians. ← Converts it to degrees and returns the result.
DIV()	→ Takes two numbers. ← Divides the numbers and returns the result.	* How to handle division by zero in SQL * How to divide columns in MySQL or Oracle * How to divide columns in SQL Server, PostgreSQL, or SQLite
EXP()	→ Takes one number. ← Calculates e to the power of the number and returns the result.
FLOOR()	→ Takes one number. ← Returns the greatest integer value that is less than or equal to the number.	* How to floor numbers in SQL
GREATEST()	→ Takes a list of numbers. ← Returns the greatest number.
LEAST()	→ Takes a list of numbers. ← Returns the smallest number.
LN()	→ Takes one number. ← Returns the natural logarithm of the number.
LOG()	→ Takes one number. ← Returns the natural logarithm of the number.
LOG10()	→ Takes one number. ← Returns the base 10 logarithm of the number.
MOD()	→ Takes two numbers. ← Divides the numbers and returns the remainder value (the modulus).	*How to get a remainder using MOD() in PostgreSQL, MS SQL Server, and MySQL
PI()	→ Takes no arguments. ← Returns the value of π.
POWER()	→ Takes two numbers. ← Raises the 1st number to the power of the 2nd number and returns the result.
RADIANS()	→ Takes a number in degrees. ← Converts it to radians and returns the result.
RAND()	→ Takes no arguments. ← Returns a random number between 0 (inclusive) and 1 (exclusive).
ROUND()	→ Takes one or two numbers. ← Rounds the 1st number to an integer or to as many decimal places as indicated by the 2nd number and returns the result.	* How to round numbers in SQL * How to use the ROUND() function in SQL
SIGN()	→ Takes one number. ← Returns a positive or negative number indicating the sign of the number.
SIN()	→ Takes one number. ← Returns the sine value in radians.
SQRT()	→ Takes one number. ← Returns the square root value of the number.	* How to calculate a square root in SQL
SQUARE()	→ Takes one number. ← Returns the squared value of the number.	* How to calculate a square in SQL
TAN()	→ Takes one number. ← Returns the tangent value in radians.
TRUNC()	→ Takes two numbers. ← Truncates the 1st number to as many decimal places as indicated by the 2nd number and returns the result.

The following sections present examples for each function.

Addition with +

The + operator returns the result of adding two numbers.

SELECT 3 + 2 AS sum;

sum
5

Subtraction with -

The - operator returns the result of subtracting two numbers.

SELECT 3 - 2 AS difference;

difference
1

Check out this article to learn more about how to subtract values in SQL.

Multiplication with *

The * operator returns the result of multiplying two numbers.

SELECT 3 * 2 AS product;

product
6

Follow this article to learn more about how to multiply values in SQL.

Division with /

The / operator returns the result of dividing two numbers.

SELECT 4 / 2 AS quotient;

quotient
2

Read these articles to learn more about:

• How the division operator works in SQL.
• How to handle division by zero in SQL.
• How to divide columns in MySQL or Oracle.
• How to divide columns in SQL Server, PostgreSQL, or SQLite.

ABS()

The ABS() function returns the absolute value of a given number.

SELECT ABS(-9) AS abs;

abs
9

Check out this article to learn more about how to compute an absolute value in SQL.

ACOS()

The ACOS() function takes a number between -1 and 1 and returns the arc cosine value in radians.

SELECT ACOS(1) AS acos;

acos
0

ASIN()

The ASIN() function takes a number between -1 and 1 and returns the arc sine value in radians.

SELECT ASIN(0) AS asin;

asin
0

ATAN()

The ATAN() function returns the arc tangent value in radians.

SELECT ATAN(0) AS atan;

atan
0

CEILING()

The CEILING() function returns the smallest integer value that is greater than or equal to a given number.

SELECT CEILING(5.5) AS ceiling;

ceiling
6

Read this article to learn more about how to round up a number to the nearest integer in SQL.

COS()

The COS() function returns the cosine value in radians.

SELECT COS(0) AS cos;

cos
1

COT()

The COT() function returns the cotangent value in radians.

SELECT COT(PI()/2) AS cot;

cot
0

DEGREES()

The DEGREES() function converts a given number from radians to degrees.

SELECT DEGREES(PI()) AS degrees;

degrees
180

DIV()

The DIV() function returns the result of dividing two numbers.

SELECT DIV(4, 2) AS div;

div
2

See these articles to learn more about:

• How to handle division by zero in SQL.
• How to divide columns in MySQL or Oracle.
• How to divide columns in SQL Server, PostgreSQL, or SQLite.
• How the division operator works in SQL.

EXP()

The EXP() function returns e to the power of a given number.

SELECT EXP(0) AS exp;

exp
1

FLOOR()

The FLOOR() function returns the greatest integer value that is less than or equal to a given number.

SELECT FLOOR(5.5) AS floor;

floor
5

Read this article to learn more about how to floor numbers in SQL.

GREATEST()

The GREATEST() function returns the greatest number from a given list.

SELECT GREATEST(1, 2, 3, 4, 5) AS greatest;

greatest
5

LEAST()

The LEAST() function returns the smallest number from a given list.

SELECT LEAST(1, 2, 3, 4, 5) AS least;

least
1

LN()

The LN() function returns the natural logarithm of a given number.

SELECT LN(1) AS ln;

ln
0

LOG()

The LOG() function returns the natural logarithm of a given number.

SELECT LOG(1) AS log;

log
0

LOG10()

The LOG10() function returns the base 10 logarithm of a given number.

SELECT LOG10(100) AS log10;

log10
2

MOD()

The MOD() function returns the remainder of the division of two given numbers.

SELECT MOD(5, 2) AS mod;

mod
1

PI()

The PI() function returns the value of π.

SELECT PI() AS pi;

pi
3.141592653589793

POWER()

The POWER() function returns the first given number raised to the power of the second given number.

SELECT POWER(2, 3) AS power;

power
8

RADIANS()

The RADIANS() function converts a given number from degrees to radians.

SELECT RADIANS(180) AS radians;

radians
3.141592653589793

RAND() or RANDOM()

The RAND() (or RANDOM()) function returns a random number between 0 (inclusive) and 1 (exclusive).

SELECT RAND() AS random;

random
0.5599700481846526

ROUND()

The ROUND() function rounds a given number to an integer or to a specified number of decimal places.

SELECT ROUND(5.4) AS round;

round
5

SELECT ROUND(5.6) AS round;

round
6

SELECT ROUND(5.4567, 2) AS round;

round
5.46

Check out these articles to learn more about how to round numbers in SQL and how to use the ROUND() function in SQL.

SIGN()

The SIGN() function returns -1 (if the argument is a negative number), 1 (if the argument is a positive number), or 0 (if the argument is a zero), indicating the sign of a given number.

SELECT SIGN(-3) AS sign;

sign
-1

SELECT SIGN(3) AS sign;

sign
1

SIN()

The SIN() function returns the sine value in radians.

SELECT SIN(PI()) AS sin;

sin
0

SQRT()

The SQRT() function returns the square root value of a given number.

SELECT SQRT(9) AS sqrt;

sqrt
3

Refer to this article to learn how to calculate a square root in SQL.

SQUARE()

The SQUARE() function returns the squared value of a given number.

SELECT SQUARE(3) AS square;

square
9

Follow this article to learn more about how to calculate a square in SQL.

TAN()

The TAN() function returns the tangent value in radians.

SELECT TAN(0) AS tan;

tan
0

TRUNC()

The TRUNC() function truncates a given number to a specified number of decimal places.

SELECT TRUNC(12.345, 1) AS trunc;

trunc
12.3

Aggregate Functions

Aggregate functions perform calculations on a set of numbers, taking all values from a column and performing calculations based on defined groups of data.

Aggregate function	Description	Further reading
AVG()	→ Takes a column of a numeric data type. ← Returns the average of all numbers.	* How to find the average of a numeric column in SQL * The SQL AVG() function explained with examples * How to filter records with AVG
COUNT()	→ Takes a column of a numeric data type. ← Returns the number of rows.	*A detailed guide to the SQL COUNT() function *What is the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT)? *How to use COUNT() with GROUP BY *How to count distinct values in SQL * How to count the number of rows in a table in SQL *How to filter records with COUNT
MAX()	→ Takes a column of a numeric data type. ← Returns the greatest number.	*SQL MIN() and MAX() functions explained in 6 examples *How to find the maximum value of a numeric column in SQL * How to find rows with the maximum value
MIN()	→ Takes a column of a numeric data type. ← Returns the smallest number.	*SQL MIN() and MAX() functions explained in 6 examples *How to find the minimum value of a column in SQL *How to find rows with the minimum value
SUM()	→ Takes a column of a numeric data type. ← Returns the sum of all numbers.	*SQL SUM() function explained with 5 practical examples *How to use SUM() with GROUP BY *How to sum values in a column in SQL *How to filter records with SUM

The following sections will present examples of each function using the numbers table:

category	number
A	1
A	2
A	3
B	4
B	5
B	6

AVG()

The AVG() function calculates the average of all values from a given column.

SELECT AVG(number) AS avg
FROM numbers;

avg
3.5

It can also return the average for each group:

SELECT category, AVG(number) AS avg
FROM numbers
GROUP BY category;

category	avg
A	2
B	5

Read these articles to see:

• More examples of the AVG() function.
• How to find the average of a numeric column in SQL.
• How to filter records with AVG.

COUNT()

The COUNT() function returns the number of non-null values in a given column.

SELECT COUNT(number) AS count
FROM numbers;

count
6

It also returns the number of values in a group:

SELECT category, COUNT(number) AS count
FROM numbers
GROUP BY category;

category	count
A	3
B	3

Further reading on this function:

• A detailed guide to the SQL COUNT() function
• How to count the number of rows in a table in SQL
• What is the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT)?
• How to use COUNT() with GROUP BY
• How to count distinct values in SQL
• How to filter records with COUNT
• How to order by count in SQL

MAX()

The MAX() function returns the greatest number from a given column.

SELECT MAX(number) AS max
FROM numbers;

max
6

It also returns the largest value per group:

SELECT category, MAX(number) AS max
FROM numbers
GROUP BY category;

category	max
A	3
B	6

Read these articles to see:

• More examples of the MAX() function.
• How to find the maximum value of a numeric column in SQL.
• How to find rows with the maximum value.

MIN()

The MIN() function returns the smallest number from a given column.

min
1

It also returns the smallest value per group:

SELECT category, MIN(number) AS min
FROM numbers
GROUP BY category;

category	min
A	1
B	4

Check out these articles for more information:

• More examples of the MIN() function.
• How to find the minimum value of a column in SQL.
• How to Find Rows with the Minimum Value.

SUM()

The SUM() function returns the sum of all values from a given column.

SELECT SUM(number) AS sum
FROM numbers;

sum
21

It also returns the sum of all values in a group:

SELECT category, SUM(number) AS sum
FROM numbers
GROUP BY category;

category	sum
A	6
B	15

Review these articles to see:

• More examples of the SUM() function.
• How to use SUM() with GROUP BY.
• How to sum values of a column in SQL.
• How to filter records with SUM.
• How to order rows by group sum in SQL.

More Help with SQL Numeric Functions

SQL provides many different functions – number functions, text functions, date and time functions, and more. For more information on them, check out our SQL cookbook and our blog articles.

Also, check out this free  SQL Functions Cheat Sheet. You can download it, print it, and keep it handy as you work and learn. Once again, I encourage you to check out our course on Standard SQL Functions if you really want to get some hands-on practice. Happy learning!