# Arithmetic Functions in SQL

Many of the uses we have for the data we retrieve involve mathematics. Most implementations of SQL provide arithmetic functions similar to the functions covered here.

## Arithmetic Functions #

The examples in this blog use the NUMCHAR table:

``````SQL> SELECT * FROM NUMCHAR;
``````
ABLASTNAMEFIRSTNAMEMCODECHARCODE
3.14154PURVISKELLYA3215
-45.707TAYLORCHUCKJ6730
59CHRISTINELAURAC6525
1555COSTALESARMANDOA7735
-7.25.3KONGMAJORG5220

### ABS #

The ABS function returns the absolute value of the number we point to. For example:

``````SQL> SELECT ABS(A) ABSOLUTE_VALUE FROM NUMCHAR;
ABSOLUTE_VALUE
--------------
3.1415
45
5
57.667
15
7.2
``````

ABS changes all the negative numbers to positive and leaves positive numbers alone.

### CEIL and FLOOR #

CEIL returns the smallest integer greater than or equal to its argument. FLOOR does just the reverse, returning the largest integer equal to or less than its argument. For example:

``````SQL> SELECT B, CEIL(B) CEILING FROM NUMCHAR;

| B    | CEILING |
| ---- | ------- |
| 4    | 4       |
| .707 | 1       |
| 99   | 42      |
| 42   | 55      |
| 55   | 55      |
| 5.3  | 6       |
``````

And

``````SQL> SELECT A, FLOOR(A) FLOOR FROM NUMCHAR;

| A       | FLOOR |
| ------- | ----- |
| 3.1415  | 3     |
| -45     | -45   |
| 5       | 5     |
| -57.667 | -58   |
| 15      | 15    |
| -7.2    | -8    |
``````

### COS, COSH, SIN, SINH, TAN, and TANH #

The COS, SIN, and TAN functions provide support for various trigonometric concepts. They all work on the assumption that n is in radians. The following statement returns some unexpected values if we don’t realize COS expects A to be in radians.

``````SQL> SELECT A, COS(A) FROM NUMCHAR;

| A       | COS(A)    |
| ------- | --------- |
| 3.1415  | -1        |
| -45     | .52532199 |
| 5       | .28366219 |
| -57.667 | .437183   |
| 15      | -.7596879 |
| -7.2    | .60835131 |
``````

Analysis: We would expect the COS of 45 degrees to be in the neighborhood of .707, not .525. To make this function work the way we would expect it to in a degree-oriented world, we need to convert degrees to radians. (When was the last time we heard a news broadcast report that a politician had done a pi-radian turn? We hear about a 180-degree turn.) Because 360 degrees - 2 pi radians, we can write

``````SQL> SELECT A, COS(A* 0.01745329251994) FROM NUMCHAR;
|
| A       | COS(A*0.01745329251994) |
| ------- | ----------------------- |
| 3.1415  | .99849724               |
| -45     | .70710678               |
| 5       | .9961947                |
| -57.667 | .5348391                |
| 15      | .96592583               |
| -7.2    | .9921147                |
``````

Analysis:

Note that the number 0.01745329251994 is radians divided by degrees. The trigonometric functions work as follows:

``````SQL> SELECT A, COS(A*0.017453), COSH(A*0.017453) FROM NUMCHAR;

| A       | COS(A*0.017453) | COSH(A*0.017453) |
| ------- | --------------- | ---------------- |
| 3.1415  | .99849729       | 1.0015035        |
| -45     | .70711609       | 1.3245977        |
| 5       | .99619483       | 1.00381          |
| -57.667 | .53485335       | 1.5507072        |
| 15      | .96592696       | 1.0344645        |
| -7.2    | .99211497       | 1.0079058        |
``````

And

``````SQL> SELECT A, SIN(A*0.017453), SINH(A*0.017453) FROM NUMCHAR;

| A       | SIN(A*0.017453) | SINH(A*0.017453) |
| ------- | --------------- | ---------------- |
| 3.1415  | .05480113       | .05485607        |
| -45     | -.7070975       | -.8686535        |
| 5       | .08715429       | .0873758         |
| -57.667 | -.8449449       | -1.185197        |
| 15      | .25881481       | .26479569        |
| -7.2    | -.1253311       | -.1259926        |
``````

And

``````SQL> SELECT A, TAN(A*0.017453), TANH(A*0.017453) FROM NUMCHAR;

| A       | TAN(A*0.017453) | TANH(A*0.017453) |
| ------- | --------------- | ---------------- |
| 3.1415  | .05488361       | .05477372        |
| -45     | -.9999737       | -.6557867        |
| 5       | .08748719       | .08704416        |
| -57.667 | -1.579769       | -.7642948        |
| 15      | .26794449       | .25597369        |
| -7.2    | -.1263272       | -.1250043        |
``````

### EXP #

EXP enables we to raise e (e is a mathematical constant used in various formulas) to a power. Here’s how EXP raises e by the values in column A:

``````SQL> SELECT A, EXP(A) FROM NUMCHAR;

| A       | EXP(A)    |
| ------- | --------- |
| 3.1415  | 23.138549 |
| -45     | 2.863E-20 |
| 5       | 148.41316 |
| -57.667 | 9.027E-26 |
| 15      | 3269017.4 |
| -7.2    | .00074659 |
``````

### LN and LOG #

These two functions center on logarithms. LN returns the natural logarithm of its argument. For example:

``````SQL> SELECT A, LN(A) FROM NUMCHAR;
ERROR:
ORA-01428: argument '-45' is out of range
``````

Did we neglect to mention that the argument had to be positive? Write

``````SQL> SELECT A, LN(ABS(A)) FROM NUMCHAR;

| A       | LN(ABS(A)) |
| ------- | ---------- |
| 3.1415  | 1.1447004  |
| -45     | 3.8066625  |
| 5       | 1.6094379  |
| -57.667 | 4.0546851  |
| 15      | 2.7080502  |
| -7.2    | 1.974081   |
``````

Analysis: Notice how we can embed the function ABS inside the LN call. The other logarith-mic function, LOG, takes two arguments, returning the logarithm of the first argument in the base of the second. The following query returns the logarithms of column B in base 10.

``````SQL> SELECT B, LOG(B, 10) FROM NUMCHAR;

| B    | LOG(B,10) |
| ---- | --------- |
| 4    | 1.660964  |
| .707 | -6.640962 |
| 9    | 1.0479516 |
| 42   | .61604832 |
| 55   | .57459287 |
| 5.3  | 1.3806894 |

``````

### MOD #

We have encountered MOD before. On Day 3, `Expressions, Conditions, and Operators`, we saw that the ANSI standard for the modulo operator % is sometimes implemented as the function MOD. Here’s a query that returns a table showing the remainder of A divided by B:

``````SQL> SELECT A, B, MOD(A,B) FROM NUMCHAR;

| A       | B    | MOD(A,B) |
| ------- | ---- | -------- |
| 3.1415  | 4    | 3.1415   |
| -45     | .707 | -.459    |
| 5       | 9    | 5        |
| -57.667 | 42   | -15.667  |
| 15      | 55   | 15       |
| -7.2    | 5.3  | -1.9     |
``````

### POWER #

To raise one number to the power of another, use POWER. In this function the first argument is raised to the power of the second:

``````SQL> SELECT A, B, POWER(A,B) FROM NUMCHAR;
ERROR:
ORA-01428: argument '-45' is out of range
``````

Analysis: ***### At first glance we are likely to think that the first argument can’t be negative. But that impression can’t be true, because a number like -4 can be raised to a power. Therefore, if the first number in the POWER function is negative, the second must be an integer. We can work around this problem by using CEIL (or FLOOR):

``````SQL> SELECT A, CEIL(B), POWER(A,CEIL(B)) FROM NUMCHAR;

| A       | CEIL(B) | POWER(A,CEIL(B)) |
| ------- | ------- | ---------------- |
| 3.1415  | 4       | 97.3976          |
| -45     | 1       | -45              |
| 5       | 9       | 1953125          |
| -57.667 | 42      | 9.098E+73        |
| 15      | 55      | 4.842E+64        |
| -7.2    | 6       | 139314.07        |
``````

### SIGN #

SIGN returns -1 if its argument is less than 0, 0 if its argument is equal to 0, and 1 if its argument is greater than 0, as shown in the following example:

``````SQL> SELECT A, SIGN(A) FROM NUMCHAR;

| A       | SIGN(A) |
| ------- | ------- |
| 3.1415  | 1       |
| -45     | -1      |
| 5       | 1       |
| -57.667 | -1      |
| 15      | 1       |
| -7.2    | -1      |
| 0       | 0       |
``````

We could also use SIGN in a SELECT WHERE clause like this:

``````SQL> SELECT A  FROM NUMCHAR WHERE SIGN(A) - 1;
A
---------
3.1415
5
15
``````

### SQRT #

The function SQRT returns the square root of an argument. Because the square root of a negative number is undefined, we cannot use SQRT on negative numbers.

``````SQL> SELECT A, SQRT(A) FROM NUMCHAR;
ERROR:
ORA-01428: argument '-45' is out of range
``````

However, we can fix this limitation with ABS:

``````SQL> SELECT ABS(A), SQRT(ABS(A)) FROM NUMCHAR;

| ABS(A) | SQRT(ABS(A)) |
| ------ | ------------ |
| 3.1415 | 1.7724277    |
| 45     | 6.7082039    |
| 5      | 2.236068     |
| 57.667 | 7.5938791    |
| 15     | 3.8729833    |
| 7.2    | 2.6832816    |
| 0      | 0            |

``````

### TRUNC #

The TRUNC function truncates the column, expression, or value to n decimal places. The TRUNC function works with arguments similar to those of the ROUND function. If the second argument is 0 or is missing, the value is truncated to zero decimal places. It the second argument is 2, the value is truncated to two decimal places. Conversely, if the second argument is -2, the value is rounded to two decimal places to the left.

``````SQL> SELECT A, TRUNC(A) FROM NUMCHAR;

| A      | TRUNC(A) |
| ------ | -------- |
| 3.1415 | 3        |
| -45    | -45      |
| 55     | -57.667  |
| -57    | -57      |
| 15     | 15       |
| -7.2   | -7       |

``````