SQL Functions - Math Functions

[chili5]

SQL Functions - Math Functions

Just like how programming languages have functions built-in to allow you to do common tasks easily. So does SQL. A lot of these functions are mathematical. Some of them are more useful than others.


ABS Function

The absolute value. The absolute value can be thought of as the distance from 0 on a number line to whatever number you have. So say your number is -5. The distance from 0 to -5 is 5. The distance from 0 to 5 is also 5. This is why the absolute value is simply the argument without any negative sign.

In math we write the absolute value as:

|a|

Now a formula for the absolute function is:

|a| = a, a >= 0
a*-1, otherwise

Programmatically, this can be written as

int abs(int a) {
	if (a >= 0) {
		return a;
	}
	return a / -1;
}

Let us take a look at how the abs function works.

Example:

SELECT ABS(-1) AS absolute;

Output:

1

Let us try passing a positive value instead.

SELECT ABS(5) AS absolute;

Output:

5

Trig Functions

The available trig functions are:

ACOS
ASIN
ATAN
TAN
COS
SIN

The first three are the inverse functions. So given a tangent value the resulting value is the angle with that tangent. The return value will be an angle in radians.

Example of COS:

SELECT COS(5.5) AS cosine;

Output:

0.70866977429126

You can confirm the results by setting your calculator to radians and taking the cosine of 5.5.

While we are looking at cosine, let us look at ACOS. This function is the inverse cosine.

Example of SIN:

SELECT SIN(3.55) AS sine;

The SIN function also takes a parameter representing an angle in radians. The resulting value is then the sine of that angle.

Output:

-0.39714816728596

Tangent

This function TAN also takes a parameter being the angle in radians. The output is the tangent of that angle.

Example:

SELECT TAN(3.5553) AS tangent;

Output:

0.43904601857096

Granted, I'm not sure why you would need trig functions in a database application but they are there. Perhaps for games?

Rounding Functions

Two functions are CEIL and CEILING. These both take a floating point parameter. These functions return the smallest integer value that is not less than the parameter.

Example:

SELECT CEIL(5.35) AS roundedUp;

Output:

6

You can think of this method as round up to the nearest integer. As for whichever you use, CEIL or CEILING they are both same function, so it comes down to personal choice.

Example of CEILING:

SELECT CEILING(5.35) AS roundedUp;

Output:

6

These functions might be useful if you have a program that keeps track of how much you send and receive from the internet. However, you want to keep the values to the nearest megabyte. So you use ceiling to get the value to the nearest megabyte.

Floor Function

The floor function allows you to round a number to the largest integer value that is less than the parameter. You can think of this function as "rounding down".

Example:

SELECT FLOOR(5.35) AS roundedDown;

The output:

5

Rounding

The function is ROUND.

This function allows you to specify how many digits you want to.

The generic formula is:

ROUND(X,Y)

This states that we are going to round X to Y decimal places.

SELECT ROUND(5.334333,2);

The result is:

5.33

Converting Radians to Degrees

For one reason or another all programming languages seem to use radians to represent angles. However, from a young age we are brought up with the concept of degrees. So degrees naturally make more sense to us. To convert from radians to degrees we use the function DEGREES.


Let's say that we have a value in radians of 0.4567 and we want to convert it to degrees.

Example:

SELECT DEGREES(0.4567) AS deg;

Output:

26.1669825036247

0.4567 radians is the same as 26.1669825036247 degrees. If we convert 26.1669825036247 back to radians, the resulting value should be 0.4567.

Converting Degrees to Radians

The function to convert to back to radians is RADIANS.

Example:

SELECT RADIANS(26.1669825036247) AS rad;

The output is:

0.4567

This confirms that radians and degrees are inverse functions.

So:

SELECT RADIANS(DEGREES(x));

The resulting value is x.

Exponents

An exponent is simply multiplying a number by itself x times. So x^n means to multiply x by itself n times. So 5^3 = 5*5*5. This is what the POW function does for us.

Example:

SELECT POW(5,3) AS pow;

Output:

125

Greatest and Max Functions

The greatest function takes a variable number of arguments and returns the largest value in the set.

Example:

SELECT GREATEST(5,3,8.5,9,-2,5,63,323,34,333);

Output:

333

If you look at the values, you will have noticed that 333 is the largest value in the set.

To demonstrate the MAX function we actually need to create a table and populate it with some data.

In a database called test, create this table:

CREATE TABLE IF NOT EXISTS `nums` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Then populate it with this data:

INSERT INTO `nums` (`id`, `age`) VALUES
(1, 3),
(2, 5);

The result:

SELECT MAX(age) FROM nums;

The output is:

5

I wish I knew about this a long time ago. It is more efficient than my method. What I would always do is use ORDER BY to sort the list in descending order and LIMIT 1 would make the result at the top the largest value.

Example:

SELECT age FROM nums ORDER BY age DESC LIMIT 1;

Output:

5

You can see the results are the same, but to get my answer I have to sort it. What if they are a lot of values? Granted I'm assuming that MAX also creates a temporary array of sorted values also, but I don't know.

Least and MIN Functions

This functions are very similar to the above functions. The MIN function selects the smallest value in a column. The least function takes a set of values and returns the smallest value in the set.

Example of least:

SELECT LEAST(5.5,3.5,2,4,-1) AS smallest;

Output:

-1

Now we can use the table we created above to demonstrate the MIN function.

SELECT MIN(age) FROM nums;

Output:

3

Looking at the table, we created, we can confirm that 3 is the smallest value in the list.

I didn't know a whole lot about SQL functions, so what I would do when I needed to do this was:

SELECT age FROM nums ORDER BY age ASC LIMIT 1;

Output:

3

This function sorts the column in ascending order and the first item is the smallest value in the column which is why I use LIMIT 1.

PI

This function simply returns the value of PI.

Example:

SELECT PI();

Output:

3.141593

Random Number

This function chooses a random floating point number between 0 and 1. We can then manipulate this number with multiplication and addition to get a seemingly random number in a range.

SELECT RAND()

One possible output:

0.234685802119491

Then we can multiply this value by some value and truncate the decimal. This allows us to limit the biggest value possible.

Example:

SELECT FLOOR(10*RAND())

We use FLOOR to truncate the decimal.

One possible output:

2

Now by adding a value to the end, allows us to control the smallest possible value generated.

SELECT FLOOR(10*RAND())+1

One possible output:

5

This function can be really useful in games.

Total Values

One really useful function is SUM. This function lets you find the total values in a column in a table. Going back to the table we created above, let us find the sum of all the ages.

SELECT SUM(age) FROM nums;

Output:

8

Average

Now, say we want to know what the average age is. This is where the AVG function is useful.

SELECT AVG(age) FROM nums;

Output:

4.0000

We get this value by adding up all the values in the column and dividing by the number of terms. In our case: (5+3)/2 = 4.

You can see how this function is more efficient than making a query to count the number of items. Then a query to find the sum. Then dividing them yourself. That is how I would have done it because I didn't know about this function.

Example:

SELECT SUM(age) / (SELECT COUNT(age) FROM nums) FROM nums;

Output:

4.0000

My method isn't wrong, it is just a bad way to do it. Why iterate twice, when you can iterate and count at the same time? I don't have that kind of control with my method. I never actually did use that code above but it is a functional but poor way of doing it.

Modulus Function

This function simply returns the remainder of integer division.

We know that 5/2 = 2. This is because with integers there is no decimal point.

If we want the remainder, wecan use the mod function to determine the remainder.

Example:

SELECT MOD(5,2);

Result:

1

Final notes

These are a lot that I didn't know about SQL. When I started my first project, I didn't know there was functions so I used other (possibly less efficient) methods to do what I wanted.

If I could, I would go back and change my program to use these functions. Granted, a lot of the functions I see no purpose for. Then again, my program was a database manager not a game or anything highly mathematical. The most math I did was adding and subtracting values. However, now I see how I could cut out a lot of queries from my program with these simple functions.

Another day we will look at inverse trigonometric functions and a more practical application of these functions.

[Jordan]

There are a ton of functions in SQL, all useful. +Rep

[BlaineSch]

Good Job! +Rep!

In "Total Values" you forgot to replace the output with the correct output.

You must spread some Reputation around before giving it to chili5 again.

[chili5]

Oops. I fixed that. Thanks!

[WingedPanther]

Nice list. Something worth noting is that these types of functions will vary slightly from one database to the next. Firebird doesn't have a sin() function by default, for example. +rep

[chili5]

So you can create your own functions then?

I'll looking into that. I wonder how that would work.

[WingedPanther]

Firebird lets you call C (or maybe C++) functions.