Arithmetic functions in SQL

We now describe the different types of arithmetic functions that we can find in SQL.

These types of functions work only with numeric data of type number, and can be divided into three main groups.

simple value functions

These functions are the simplest and work with a single number, variable, or table column.

Function Purpose ABS(n) Returns the absolute value of n CEIL(n) Returns the integer value equal to or immediately greater than an FLOOR(n) Returns the integer value equal to or immediately less than an MOD(m,n) Returns the remainder of the division of m by n POWER(m, exponent) Calculates the power of m raised to an exponent SIGN(value) Returns the sign of value NVL(value, expression) Function that replaces value by expression whenever value is NULL ROUND(number) Rounds numbers to decimal places SQRT(n) Returns the square root of n TRUNC(number) Truncates numbers so that they have m decimal places.

We will give some examples so that it is clearer how they are used:

Select ceil(2.3) from table; (this query would return 3)

Select mod (11,4) from table; (would return 3)

Select round(22.38,1) from table; (would return 22.4)

Value group functions

These types of functions are mainly used for statistics, so null values ​​are not taken into account.

Within this group are the following functions:

Function Purpose AVG(n) Returns the mean of n COUNT(*|expression) Returns the number of times that expression appears. MAX(expression) Returns the maximum value of the expression MIN (expression) Returns the minimum value of the expression VARIANCE(expression) Returns the variance of the expression SUM(expression) Returns the sum of the values ​​of the expression.

Let’s give some examples:

Select avg(salary) from employee; (it would return the average salary of all employees)

Select count

from employee; (it returns the number of employees we have)

Select min(salary) from employee; (it returns the lowest salary found in the employee table)

list functions

These functions work with groups of columns within the same row.

Within this group of functions are the following:

Function Purpose GREATEST(value1, value2,…) Gets the greatest value from the list LEAST(value1, value2,…) Gets the least value from the list

To explain these functions we are going to give a slightly more complicated example, where the statement would be the following:

We want to show for each student his highest note of all that he has.

Select student, greatest(grade1,grade2,grade2,grade4,grade5) “grade” from students;