# 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.

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

### Multiplication with *

The `*` operator returns the result of multiplying two numbers.

`SELECT 3 * 2 AS product;`
product
6

### Division with /

The `/` operator returns the result of dividing two numbers.

`SELECT 4 / 2 AS quotient;`
quotient
2

### ABS()

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

`SELECT ABS(-9) AS abs;`
abs
9

### 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

### 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

### 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

### 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

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

`SELECT RADIANS(180) AS 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

### 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. COUNT() → Takes a column of a numeric data type. ← Returns the number of rows. MAX() → Takes a column of a numeric data type. ← Returns the greatest number. MIN() → Takes a column of a numeric data type. ← Returns the smallest number. SUM() → Takes a column of a numeric data type. ← Returns the sum of all numbers.

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

categorynumber
A1
A2
A3
B4
B5
B6

### 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;
```
categoryavg
A2
B5

Read these articles to see:

### 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;
```
categorycount
A3
B3

Further reading on this function:

### 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;
```
categorymax
A3
B6

Read these articles to see:

### 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;
```
categorymin
A1
B4

Check out these articles for more information:

### 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;
```
categorysum
A6
B15

Review these articles to see:

## 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!