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

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
SELECT CEILING(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
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
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
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
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
SELECT TRUNCATE(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
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
SELECT ABS(-69), ABS(0), ABS(69);
ABS(-69)ABS(0)ABS(69)
69069

We are in touch with you
English