Numeric data type in SQL

Creating numeric data in SQL is quite simple: you can enter a number as a literal, you can get it from a table column, or generate it by calculation.

When calculating, you can use all standard arithmetic operations (+, -, *, / and others) and change the priorities of calculations using brackets.

MySQL 8.1
SELECT 2 * ((22 - 16) / (2 + 1)) AS calc_example;
calc_example
4

Math functions

For most mathematical calculations, such as getting the power of a number or getting the square root, in SQL there are built-in numeric functions. Here are some examples of these functions:

Function nameDescription
POW(num, power)Calculates a number to the specified power
SQRT(num)Calculates the square root of a number
LOG(base, num)Calculates the logarithm of a number to the specified base
EXP(num)Calculates enum
SIN(num)Calculates the sine of a number
COS(num)Calculates the cosine of a number
TAN(num)Calculates the tangent of a number

A list of all numeric functions, their descriptions and examples can be found in the handbook.

Function nameDescription
POWER(num, power)Calculates a number to the specified power
SQRT(num)Calculates the square root of a number
LOG(base, num)Calculates the logarithm of a number to the specified base
EXP(num)Calculates enum
SIN(num)Calculates the sine of a number
COS(num)Calculates the cosine of a number
TAN(num)Calculates the tangent of a number

A list of all numeric functions, their descriptions and examples can be found in the handbook.

Round numbers

When working with floating point numbers, it is not always necessary to store or display numbers with full precision. So, monetary transactions can be stored with an accuracy of up to 6 decimal places, and displayed up to 2, with an accuracy of kopecks.

SQL provides the following 4 functions for rounding numeric data: CEIL, FLOOR, ROUND, TRUNCATE.

The functions CEIL, FLOOR are aimed at rounding a number to the nearest integer up and down, respectively.

MySQL 8.1
SELECT CEILING(69.69) AS ceiling, FLOOR(69.69) AS floor;

SQL provides the following 4 functions for rounding numeric data: CEIL, FLOOR, ROUND, TRUNC.

The functions CEIL, FLOOR are aimed at rounding a number to the nearest integer up and down, respectively.

MySQL 8.1
SELECT CEIL(69.69) AS ceiling, FLOOR(69.69) AS floor;
ceilingfloor
7069

To round to the nearest integer, there is a ROUND function, which rounds any number whose decimal part is greater than or equal to 0.5. side, otherwise less.

MySQL 8.1
SELECT ROUND(69.499), ROUND(69.5), ROUND(69.501);
ROUND(69.499)ROUND(69.5)ROUND(69.501)
697070

The ROUND function also allows you to round a number to some fraction of decimal places. To do this, the function takes an optional second argument indicating the number of decimal places to leave.

MySQL 8.1
SELECT ROUND(69.7171,1), ROUND(69.7171,2), ROUND(69.7171,3);
ROUND(69.7171,1)ROUND(69.7171,2)ROUND(69.7171,3)
69.769.7269.717

The second argument to the ROUND function can also take negative values. In this case, the digits to the left of the decimal point of the number become equal to zero by the number specified in the argument, and the fractional part is cut off.

MySQL 8.1
SELECT ROUND(1691.7,-1), ROUND(1691.7,-2), ROUND(1691.7,-3);
ROUND(1691.7,-1)ROUND(1691.7,-2)ROUND(1691.7,-3)
169017002000

The TRUNCATE function is similar to the ROUND function, it is also capable of taking an optional 2nd parameter, only instead of rounding it simply discards unnecessary numbers.

MySQL 8.1
SELECT TRUNCATE(69.7979,1), TRUNCATE(69.7979,2), TRUNCATE(69.7979,3);
TRUNCATE(69.7979,1)TRUNCATE(69.7979,2)TRUNCATE(69.7979,3)
69.769.7969.797

What will the following expression return?

MySQL 8.1
SELECT TRUNCATE(69.7979, -1);

The TRUNC function is similar to the ROUND function, it is also capable of taking an optional 2nd parameter, only instead of rounding it simply discards unnecessary numbers.

MySQL 8.1
SELECT TRUNC(69.7979,1), TRUNC(69.7979,2), TRUNC(69.7979,3);
TRUNC(69.7979,1)TRUNC(69.7979,2)TRUNC(69.7979,3)
69.769.7969.797

What will the following expression return?

MySQL 8.1
SELECT TRUNC(69.7979, -1);

Working with signed numbers

When working with numeric data that may contain negative values, the SIGN and ABS functions can be useful.

The SIGN function returns -1 if the number is negative, 0 if the number is zero, and 1 if the number is positive.

MySQL 8.1
SELECT SIGN(-69), SIGN(0), SIGN(69);
SIGN(-69)SIGN(0)SIGN(69)
-101

The ABS function returns the absolute value of a number.

MySQL 8.1
SELECT ABS(-69), ABS(0), ABS(69);
ABS(-69)ABS(0)ABS(69)
69069