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;

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:

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;

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);

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);

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);

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);

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);

The ABS function returns the absolute value of a number.

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