# Math functions
<a name="Math_functions-spark"></a>

This section describes the mathematical operators and functions supported in AWS Clean Rooms Spark SQL. 

**Topics**
+ [Mathematical operator symbols](OPERATOR_SYMBOLS.md)
+ [ABS function](ABS.md)
+ [ACOS function](ACOS.md)
+ [ASIN function](ASIN.md)
+ [ATAN function](ATAN.md)
+ [ATAN2 function](ATAN2.md)
+ [CBRT function](CBRT.md)
+ [CEILING (or CEIL) function](CEILING_FLOOR.md)
+ [COS function](COS.md)
+ [COT function](COT.md)
+ [DEGREES function](DEGREES.md)
+ [DIV function](DIV.md)
+ [EXP function](EXP.md)
+ [FLOOR function](FLOOR.md)
+ [LN function](LN.md)
+ [LOG function](LOG.md)
+ [MOD function](MOD.md)
+ [PI function](PI.md)
+ [POWER function](POWER.md)
+ [RADIANS function](RADIANS.md)
+ [RAND function](RAND.md)
+ [RANDOM function](RANDOM.md)
+ [ROUND function](ROUND.md)
+ [SIGN function](SIGN.md)
+ [SIN function](SIN.md)
+ [SQRT function](SQRT.md)
+ [TRUNC function](TRUNC.md)

# Mathematical operator symbols
<a name="OPERATOR_SYMBOLS"></a>

 The following table lists the supported mathematical operators. 

## Supported operators
<a name="OPERATOR_SYMBOLS-supported-operators"></a>

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/OPERATOR_SYMBOLS.html)

## Examples
<a name="OPERATOR_SYMBOLS-examples"></a>

Calculate the commission paid plus a \$12.00 handling fee for a given transaction: 

```
select commission, (commission + 2.00) as comm
from sales where salesid=10000;

commission | comm
-----------+-------
28.05      | 30.05
(1 row)
```

Calculate 20 percent of the sales price for a given transaction: 

```
select pricepaid, (pricepaid * .20) as twentypct
from sales where salesid=10000;

pricepaid | twentypct
----------+-----------
187.00    |    37.400
(1 row)
```

Forecast ticket sales based on a continuous growth pattern. In this example, the subquery returns the number of tickets sold in 2008. That result is multiplied exponentially by a continuous growth rate of 5 percent over 10 years. 

```
select (select sum(qtysold) from sales, date
where sales.dateid=date.dateid and year=2008)
^ ((5::float/100)*10) as qty10years;

qty10years
------------------
587.664019657491
(1 row)
```

Find the total price paid and commission for sales with a date ID that is greater than or equal to 2,000. Then subtract the total commission from the total price paid. 

```
select sum (pricepaid) as sum_price, dateid,
sum (commission) as sum_comm, (sum (pricepaid) - sum (commission)) as value
from sales where dateid >= 2000
group by dateid order by dateid limit 10;

 sum_price | dateid | sum_comm |   value
-----------+--------+----------+-----------
 364445.00 |   2044 | 54666.75 | 309778.25
 349344.00 |   2112 | 52401.60 | 296942.40
 343756.00 |   2124 | 51563.40 | 292192.60
 378595.00 |   2116 | 56789.25 | 321805.75
 328725.00 |   2080 | 49308.75 | 279416.25
 349554.00 |   2028 | 52433.10 | 297120.90
 249207.00 |   2164 | 37381.05 | 211825.95
 285202.00 |   2064 | 42780.30 | 242421.70
 320945.00 |   2012 | 48141.75 | 272803.25
 321096.00 |   2016 | 48164.40 | 272931.60
(10 rows)
```

# ABS function
<a name="ABS"></a>

 ABS calculates the absolute value of a number, where that number can be a literal or an expression that evaluates to a number. 

## Syntax
<a name="ABS-synopsis"></a>

```
ABS (number)
```

## Arguments
<a name="ABS-arguments"></a>

 *number*   
Number or expression that evaluates to a number. It can be the SMALLINT, INTEGER, BIGINT, DECIMAL, FLOAT4, or FLOAT8 type.

## Return type
<a name="ABS-return-type"></a>

ABS returns the same data type as its argument. 

## Examples
<a name="ABS-examples"></a>

Calculate the absolute value of -38: 

```
select abs (-38);
abs
-------
38
(1 row)
```

Calculate the absolute value of (14-76): 

```
select abs (14-76);
abs
-------
62
(1 row)
```

# ACOS function
<a name="ACOS"></a>

ACOS is a trigonometric function that returns the arc cosine of a number. The return value is in radians and is between `0` and `PI`.

## Syntax
<a name="ACOS-synopsis"></a>

```
ACOS(number)
```

## Arguments
<a name="ACOS-arguments"></a>

 *number*   
The input parameter is a `DOUBLE PRECISION` number. 

## Return type
<a name="ACOS-return-type"></a>

`DOUBLE PRECISION`

## Examples
<a name="ACOS-examples"></a>

To return the arc cosine of `-1`, use the following example. 

```
SELECT ACOS(-1);

+-------------------+
|       acos        |
+-------------------+
| 3.141592653589793 |
+-------------------+
```

# ASIN function
<a name="ASIN"></a>

ASIN is a trigonometric function that returns the arc sine of a number. The return value is in radians and is between `PI/2` and `-PI/2`. 

## Syntax
<a name="ASIN-synopsis"></a>

```
ASIN(number)
```

## Arguments
<a name="ASIN-argument"></a>

 *number*   
The input parameter is a `DOUBLE PRECISION` number. 

## Return type
<a name="ASIN-return-type"></a>

`DOUBLE PRECISION`

## Examples
<a name="ASIN-examples"></a>

To return the arc sine of `1`, use the following example. 

```
SELECT ASIN(1) AS halfpi;

+--------------------+
|       halfpi       |
+--------------------+
| 1.5707963267948966 |
+--------------------+
```

# ATAN function
<a name="ATAN"></a>

ATAN is a trigonometric function that returns the arc tangent of a number. The return value is in radians and is between `-PI` and `PI`.

## Syntax
<a name="ATAN-synopsis"></a>

```
ATAN(number)
```

## Arguments
<a name="ATAN-argument"></a>

 *number*   
The input parameter is a `DOUBLE PRECISION` number. 

## Return type
<a name="ATAN-return-type"></a>

`DOUBLE PRECISION`

## Examples
<a name="ATAN-examples"></a>

To return the arc tangent of `1` and multiply it by 4, use the following example.

```
SELECT ATAN(1) * 4 AS pi;
            
+-------------------+
|        pi         |
+-------------------+
| 3.141592653589793 |
+-------------------+
```

# ATAN2 function
<a name="ATAN2"></a>

ATAN2 is a trigonometric function that returns the arc tangent of one number divided by another number. The return value is in radians and is between `PI/2` and `-PI/2`. 

## Syntax
<a name="ATAN2-synopsis"></a>

```
ATAN2(number1, number2)
```

## Arguments
<a name="ATAN2-arguments"></a>

 *number1*   
A `DOUBLE PRECISION` number. 

 *number2*   
A `DOUBLE PRECISION` number. 

## Return type
<a name="ATAN2-return-type"></a>

`DOUBLE PRECISION`

## Examples
<a name="ATAN2-examples"></a>

To return the arc tangent of `2/2` and multiply it by 4, use the following example. 

```
SELECT ATAN2(2,2) * 4 AS PI;

+-------------------+
|        pi         |
+-------------------+
| 3.141592653589793 |
+-------------------+
```

# CBRT function
<a name="CBRT"></a>

 The CBRT function is a mathematical function that calculates the cube root of a number. 

## Syntax
<a name="CBRT-synopsis"></a>

```
CBRT (number)
```

## Argument
<a name="CBRT-argument"></a>

CBRT takes a DOUBLE PRECISION number as an argument. 

## Return type
<a name="CBRT-return-type"></a>

CBRT returns a DOUBLE PRECISION number. 

## Examples
<a name="CBRT-examples"></a>

Calculate the cube root of the commission paid for a given transaction: 

```
select cbrt(commission) from sales where salesid=10000;

cbrt
------------------
3.03839539048843
(1 row)
```

# CEILING (or CEIL) function
<a name="CEILING_FLOOR"></a>

The CEILING or CEIL function is used to round a number up to the next whole number. (The [FLOOR function](FLOOR.md) rounds a number down to the next whole number.) 

## Syntax
<a name="CEILING_FLOOR-synopsis"></a>

```
CEIL | CEILING(number)
```

## Arguments
<a name="CEILING_FLOOR-arguments"></a>

 *number*   
The number or expression that evaluates to a number. It can be the SMALLINT, INTEGER, BIGINT, DECIMAL, FLOAT4, or FLOAT8 type.

## Return type
<a name="CEILING_FLOOR-return-type"></a>

CEILING and CEIL return the same data type as its argument. 

## Example
<a name="CEILING_FLOOR-example"></a>

Calculate the ceiling of the commission paid for a given sales transaction: 

```
select ceiling(commission) from sales
where salesid=10000;

ceiling
---------
29
(1 row)
```

# COS function
<a name="COS"></a>

COS is a trigonometric function that returns the cosine of a number. The return value is in radians and is between `-1` and `1`, inclusive. 

## Syntax
<a name="COS-synopsis"></a>

```
COS(double_precision)
```

## Argument
<a name="COS-argument"></a>

 *number*   
The input parameter is a double precision number. 

## Return type
<a name="COS-return-type"></a>

The COS function returns a double precision number. 

## Examples
<a name="COS-examples"></a>

The following example returns cosine of 0: 

```
select cos(0);
cos
-----
1
(1 row)
```

The following example returns the cosine of PI: 

```
select cos(pi());
cos
-----
-1
(1 row)
```

# COT function
<a name="COT"></a>

COT is a trigonometric function that returns the cotangent of a number. The input parameter must be nonzero. 

## Syntax
<a name="COT-synopsis"></a>

```
COT(number)
```

## Argument
<a name="COT-argument"></a>

 *number*   
The input parameter is a `DOUBLE PRECISION` number. 

## Return type
<a name="COT-return-type"></a>

`DOUBLE PRECISION`

## Examples
<a name="COT-examples"></a>

To return the cotangent of 1, use the following example. 

```
SELECT COT(1);

+--------------------+
|        cot         |
+--------------------+
| 0.6420926159343306 |
+--------------------+
```

# DEGREES function
<a name="DEGREES"></a>

Converts an angle in radians to its equivalent in degrees. 

## Syntax
<a name="DEGREES-synopsis"></a>

```
DEGREES(number)
```

## Argument
<a name="DEGREES-argument"></a>

 *number*   
The input parameter is a `DOUBLE PRECISION` number. 

## Return type
<a name="DEGREES-return-type"></a>

`DOUBLE PRECISION`

## Example
<a name="DEGREES-examples"></a>

To return the degree equivalent of .5 radians, use the following example. 

```
SELECT DEGREES(.5);

+-------------------+
|      degrees      |
+-------------------+
| 28.64788975654116 |
+-------------------+
```

To convert PI radians to degrees, use the following example. 

```
SELECT DEGREES(pi());

+---------+
| degrees |
+---------+
|     180 |
+---------+
```

# DIV function
<a name="DIV"></a>

The DIV operator returns the integral part of the division of dividend by divisor. 

## Syntax
<a name="DIV-syntax"></a>

```
dividend div divisor
```

## Arguments
<a name="DIV-arguments"></a>

 *dividend*   
An expression that evaluates to a numeric or interval.

*divisor*  
A matching interval type if `dividend` is an interval, a numeric otherwise.

## Return type
<a name="DIV-returns"></a>

`BIGINT`

## Examples
<a name="DIV-examples"></a>

The following example selects two columns from the squirrels table: the `id` column, which contains the unique identifier for each squirrel, and a `calculated` column, `age div 2`, which represents the integer division of the age column by 2. The `age div 2` calculation performs integer division on the `age` column, effectively rounding down the age to the nearest even integer. For example, if the `age` column contains values like 3, 5, 7, and 10, the `age div 2` column would contain the values 1, 2, 3, and 5, respectively. 

```
SELECT id, age div 2 FROM squirrels
```

This query can be useful in scenarios where you need to group or analyze data based on age ranges, and you want to simplify the age values by rounding them down to the nearest even integer. The resulting output would provide the `id` and the age divided by 2 for each squirrel in the `squirrels` table. 

# EXP function
<a name="EXP"></a>

The EXP function implements the exponential function for a numeric expression, or the base of the natural logarithm, `e`, raised to the power of expression. The EXP function is the inverse of [LN function](LN.md). 

## Syntax
<a name="EXP-synopsis"></a>

```
EXP (expression)
```

## Argument
<a name="EXP-argument"></a>

 *expression*   
The expression must be an INTEGER, DECIMAL, or DOUBLE PRECISION data type. 

## Return type
<a name="EXP-return-type"></a>

EXP returns a DOUBLE PRECISION number. 

## Example
<a name="EXP-example"></a>

Use the EXP function to forecast ticket sales based on a continuous growth pattern. In this example, the subquery returns the number of tickets sold in 2008. That result is multiplied by the result of the EXP function, which specifies a continuous growth rate of 7% over 10 years. 

```
select (select sum(qtysold) from sales, date
where sales.dateid=date.dateid
and year=2008) * exp((7::float/100)*10) qty2018;

qty2018
------------------
695447.483772222
(1 row)
```

# FLOOR function
<a name="FLOOR"></a>

The FLOOR function rounds a number down to the next whole number. 

## Syntax
<a name="FLOOR-synopsis"></a>

```
FLOOR (number)
```

## Argument
<a name="FLOOR-argument"></a>

 *number*   
The number or expression that evaluates to a number. It can be the SMALLINT, INTEGER, BIGINT, DECIMAL, FLOAT4, or FLOAT8 type. 

## Return type
<a name="FLOOR-return-type"></a>

FLOOR returns the same data type as its argument. 

## Example
<a name="FLOOR-example"></a>

The example shows the value of the commission paid for a given sales transaction before and after using the FLOOR function. 

```
select commission from sales
where salesid=10000;

floor
-------
28.05
(1 row)

select floor(commission) from sales
where salesid=10000;

floor
-------
28
(1 row)
```

# LN function
<a name="LN"></a>

The LN function returns the natural logarithm of the input parameter. 

## Syntax
<a name="LN-synopsis"></a>

```
LN(expression)
```

## Argument
<a name="LN-argument"></a>

 *expression*   
The target column or expression that the function operates on.   
This function returns an error for some data types if the expression references an AWS Clean Rooms user-created table or an AWS Clean Rooms STL or STV system table. 
Expressions with the following data types produce an error if they reference a user-created or system table.  
+ BOOLEAN 
+ CHAR 
+ DATE 
+ DECIMAL or NUMERIC 
+ TIMESTAMP 
+ VARCHAR 
Expressions with the following data types run successfully on user-created tables and STL or STV system tables:   
+ BIGINT 
+ DOUBLE PRECISION 
+ INTEGER 
+ REAL 
+ SMALLINT 

## Return type
<a name="LN-return-type"></a>

The LN function returns the same type as the expression. 

## Example
<a name="LN-example"></a>

The following example returns the natural logarithm, or base e logarithm, of the number 2.718281828: 

```
select ln(2.718281828);
ln
--------------------
0.9999999998311267
(1 row)
```

Note that the answer is nearly equal to 1. 

This example returns the natural logarithm of the values in the USERID column in the USERS table: 

```
select username, ln(userid) from users order by userid limit 10;

 username |        ln
----------+-------------------
 JSG99FHE |                 0
 PGL08LJI | 0.693147180559945
 IFT66TXU |  1.09861228866811
 XDZ38RDD |  1.38629436111989
 AEB55QTM |   1.6094379124341
 NDQ15VBM |  1.79175946922805
 OWY35QYB |  1.94591014905531
 AZG78YIP |  2.07944154167984
 MSD36KVR |  2.19722457733622
 WKW41AIW |  2.30258509299405
(10 rows)
```

# LOG function
<a name="LOG"></a>

Returns the logarithm of `expr` with `base`.

## Syntax
<a name="LOG-synopsis"></a>

```
LOG(base, expr)
```

## Argument
<a name="LOG-argument"></a>

 *expr*   
The expression must have an integer, decimal, or floating-point data type. 

 *base*   
The base for the logarithm calculation. Must be a positive number (not equal to 1) of double precision data type. 

## Return type
<a name="LOG-return-type"></a>

The LOG function returns a double precision number. 

## Example
<a name="LOG-example"></a>

The following example returns the base 10 logarithm of the number 100: 

```
select log(10, 100);
--------
2
(1 row)
```

# MOD function
<a name="MOD"></a>

Returns the remainder of two numbers, otherwise known as a *modulo* operation. To calculate the result, the first parameter is divided by the second.

## Syntax
<a name="MOD-synopsis"></a>

```
MOD(number1, number2)
```

## Arguments
<a name="MOD-arguments"></a>

 *number1*   
The first input parameter is an INTEGER, SMALLINT, BIGINT, or DECIMAL number. If either parameter is a DECIMAL type, the other parameter must also be a DECIMAL type. If either parameter is an INTEGER, the other parameter can be an INTEGER, SMALLINT, or BIGINT. Both parameters can also be SMALLINT or BIGINT, but one parameter cannot be a SMALLINT if the other is a BIGINT. 

 *number2*   
The second parameter is an INTEGER, SMALLINT, BIGINT, or DECIMAL number. The same data type rules apply to *number2* as to *number1*. 

## Return type
<a name="MOD-return-type"></a>

Valid return types are DECIMAL, INT, SMALLINT, and BIGINT. The return type of the MOD function is the same numeric type as the input parameters, if both input parameters are the same type. If either input parameter is an INTEGER, however, the return type will also be an INTEGER. 

## Usage notes
<a name="MOD-usage-notes"></a>

You can use *%* as a modulo operator.

## Examples
<a name="MOD-example"></a>

The following example return the remainder when a number is divided by another:

```
SELECT MOD(10, 4);
               
 mod
------
 2
```

The following example returns a decimal result:

```
SELECT MOD(10.5, 4);
               
 mod
------
 2.5
```

You can cast parameter values:

```
SELECT MOD(CAST(16.4 as integer), 5);
               
 mod
------
 1
```

Check if the first parameter is even by dividing it by 2:

```
SELECT mod(5,2) = 0 as is_even;
               
 is_even
--------
 false
```

You can use the *%* as a modulo operator:

```
SELECT 11 % 4 as remainder;
               
 remainder
-----------
 3
```

The following example returns information for odd-numbered categories in the CATEGORY table: 

```
select catid, catname
from category
where mod(catid,2)=1
order by 1,2;

 catid |  catname
-------+-----------
     1 | MLB
     3 | NFL
     5 | MLS
     7 | Plays
     9 | Pop
    11 | Classical

(6 rows)
```

# PI function
<a name="PI"></a>

The PI function returns the value of pi to 14 decimal places. 

## Syntax
<a name="PI-synopsis"></a>

```
PI()
```

## Return type
<a name="PI-return-type"></a>

`DOUBLE PRECISION`

## Examples
<a name="PI-examples"></a>

To return the value of pi, use the following example.

```
SELECT PI();

+-------------------+
|        pi         |
+-------------------+
| 3.141592653589793 |
+-------------------+
```

# POWER function
<a name="POWER"></a>

 The POWER function is an exponential function that raises a numeric expression to the power of a second numeric expression. For example, 2 to the third power is calculated as `POWER(2,3)`, with a result of `8`. 

## Syntax
<a name="POWER-synopsis"></a>

```
{POWER(expression1, expression2)
```

## Arguments
<a name="POWER-arguments"></a>

 *expression1*   
Numeric expression to be raised. Must be an `INTEGER`, `DECIMAL`, or `FLOAT` data type. 

 *expression2*   
Power to raise *expression1*. Must be an `INTEGER`, `DECIMAL`, or `FLOAT` data type. 

## Return type
<a name="POWER-return-type"></a>

`DOUBLE PRECISION`

## Example
<a name="POWER-examples"></a>

```
SELECT (SELECT SUM(qtysold) FROM sales, date
WHERE sales.dateid=date.dateid
AND year=2008) * POW((1+7::FLOAT/100),10) qty2010;

+-------------------+
|      qty2010      |
+-------------------+
| 679353.7540885945 |
+-------------------+
```

# RADIANS function
<a name="RADIANS"></a>

The RADIANS function converts an angle in degrees to its equivalent in radians. 

## Syntax
<a name="RADIANS-synopsis"></a>

```
RADIANS(number)
```

## Argument
<a name="RADIANS-argument"></a>

 *number*   
The input parameter is a `DOUBLE PRECISION` number. 

## Return type
<a name="RADIANS-return-type"></a>

`DOUBLE PRECISION`

## Example
<a name="RADIANS-examples"></a>

To return the radian equivalent of 180 degrees, use the following example. 

```
SELECT RADIANS(180);

+-------------------+
|      radians      |
+-------------------+
| 3.141592653589793 |
+-------------------+
```

# RAND function
<a name="RAND"></a>

The RAND function generates a random floating-point number between 0 and 1. The RAND function generates a new random number each time it's called.

## Syntax
<a name="RAND-syntax"></a>

```
RAND()
```

## Return type
<a name="RAND-return-type"></a>

RANDOM returns a DOUBLE. 

## Example
<a name="RAND-example"></a>

The following example generates a column of random floating-point numbers between 0 and 1 for each row in the `squirrels` table. The resulting output would be a single column containing a list of random decimal values, with one value for each row in the squirrels table. 

```
SELECT rand() FROM squirrels
```

This type of query is useful when you need to generate random numbers, for example, to simulate random events or to introduce randomness into your data analysis. In the context of the `squirrels` table, it might be used to assign random values to each squirrel, which could then be used for further processing or analysis.

# RANDOM function
<a name="RANDOM"></a>

The RANDOM function generates a random value between 0.0 (inclusive) and 1.0 (exclusive). 

## Syntax
<a name="RANDOM-synopsis"></a>

```
RANDOM()
```

## Return type
<a name="RANDOM-return-type"></a>

RANDOM returns a DOUBLE PRECISION number. 

## Examples
<a name="RANDOM-examples"></a>

1. Compute a random value between 0 and 99. If the random number is 0 to 1, this query produces a random number from 0 to 100: 

   ```
   select cast (random() * 100 as int);
   
   INTEGER
   ------
   24
   (1 row)
   ```

1. Retrieve a uniform random sample of 10 items:

   ```
   select * 
   from sales
   order by random()
   limit 10;
   ```

   Now retrieve a random sample of 10 items, but choose the items in proportion to their prices. For example, an item that is twice the price of another would be twice as likely to appear in the query results:

   ```
   select * 
   from sales
   order by log(1 - random()) / pricepaid
   limit 10;
   ```

1. This example uses the SET command to set a SEED value so that RANDOM generates a predictable sequence of numbers. 

   First, return three RANDOM integers without setting the SEED value first: 

   ```
   select cast (random() * 100 as int);
   INTEGER
   ------
   6
   (1 row)
   
   select cast (random() * 100 as int);
   INTEGER
   ------
   68
   (1 row)
   
   select cast (random() * 100 as int);
   INTEGER
   ------
   56
   (1 row)
   ```

   Now, set the SEED value to `.25`, and return three more RANDOM numbers: 

   ```
   set seed to .25;
   select cast (random() * 100 as int);
   INTEGER
   ------
   21
   (1 row)
   
   select cast (random() * 100 as int);
   INTEGER
   ------
   79
   (1 row)
   
   select cast (random() * 100 as int);
   INTEGER
   ------
   12
   (1 row)
   ```

   Finally, reset the SEED value to `.25`, and verify that RANDOM returns the same results as the previous three calls: 

   ```
   set seed to .25;
   select cast (random() * 100 as int);
   INTEGER
   ------
   21
   (1 row)
   
   select cast (random() * 100 as int);
   INTEGER
   ------
   79
   (1 row)
   
   select cast (random() * 100 as int);
   INTEGER
   ------
   12
   (1 row)
   ```

# ROUND function
<a name="ROUND"></a>

The ROUND function rounds numbers to the nearest integer or decimal. 

The ROUND function can optionally include a second argument as an integer to indicate the number of decimal places for rounding, in either direction. When you don't provide the second argument, the function rounds to the nearest whole number. When the second argument *>n* is specified, the function rounds to the nearest number with *n* decimal places of precision. 

## Syntax
<a name="ROUND-synopsis"></a>

```
ROUND (number [ , integer ] )
```

## Argument
<a name="ROUND-argument"></a>

 *number*   
A number or expression that evaluates to a number. It can be the DECIMAL or FLOAT8 type. AWS Clean Rooms can convert other data types per the implicit conversion rules. 

*integer* (optional)  
An integer that indicates the number of decimal places for rounding in either directions. 

## Return type
<a name="ROUND-return-type"></a>

ROUND returns the same numeric data type as the input argument(s). 

## Examples
<a name="ROUND-examples"></a>

Round the commission paid for a given transaction to the nearest whole number. 

```
select commission, round(commission)
from sales where salesid=10000;

commission | round
-----------+-------
     28.05 |    28
(1 row)
```

Round the commission paid for a given transaction to the first decimal place. 

```
select commission, round(commission, 1)
from sales where salesid=10000;

commission | round
-----------+-------
     28.05 |  28.1
(1 row)
```

For the same query, extend the precision in the opposite direction. 

```
select commission, round(commission, -1)
from sales where salesid=10000;

commission | round
-----------+-------
     28.05 |    30
(1 row)
```

# SIGN function
<a name="SIGN"></a>

 The SIGN function returns the sign (positive or negative) of a number. The result of the SIGN function is `1`, `-1`, or `0` indicating the sign of the argument. 

## Syntax
<a name="SIGN-synopsis"></a>

```
SIGN (number)
```

## Argument
<a name="SIGN-argument"></a>

 *number*   
Number or expression that evaluates to a number. It can be the DECIMALor FLOAT8 type. AWS Clean Rooms can convert other data types per the implicit conversion rules.

## Return type
<a name="SIGN-return-type"></a>

SIGN returns the same numeric data type as the input argument(s). If the input is DECIMAL, the output is DECIMAL(1,0). 

## Examples
<a name="SIGN-examples"></a>

To determine the sign of the commission paid for a given transaction from the SALES table, use the following example. 

```
SELECT commission, SIGN(commission)
FROM sales WHERE salesid=10000;

+------------+------+
| commission | sign |
+------------+------+
|      28.05 |    1 |
+------------+------+
```

# SIN function
<a name="SIN"></a>

SIN is a trigonometric function that returns the sine of a number. The return value is between `-1` and `1`. 

## Syntax
<a name="SIN-synopsis"></a>

```
SIN(number)
```

## Argument
<a name="SIN-argument"></a>

 *number*   
A `DOUBLE PRECISION` number in radians. 

## Return type
<a name="SIN-return-type"></a>

`DOUBLE PRECISION` 

## Example
<a name="SIN-examples"></a>

To return the sine of `-PI`, use the following example.

```
SELECT SIN(-PI());

+-------------------------+
|           sin           |
+-------------------------+
| -0.00000000000000012246 |
+-------------------------+
```

# SQRT function
<a name="SQRT"></a>

 The SQRT function returns the square root of a numeric value. The square root is a number multiplied by itself to get the given value.

## Syntax
<a name="SQRT-synopsis"></a>

```
SQRT (expression)
```

## Argument
<a name="SQRT-argument"></a>

 *expression*   
The expression must have an integer, decimal, or floating-point data type. The expression can include functions. The system might perform implicit type conversions. 

## Return type
<a name="SQRT-return-type"></a>

SQRT returns a DOUBLE PRECISION number.

## Examples
<a name="SQRT-examples"></a>

The following example returns the square root of a number. 

```
select sqrt(16);
               
sqrt
---------------
4
```

The following example performs an implicit type conversion.

```
select sqrt('16');
               
sqrt
---------------
4
```

The following example nests functions to perform a more complex task. 

```
select sqrt(round(16.4)); 

sqrt
---------------
4
```

The following example results in the length of the radius when given the area of a circle. It calculates the radius in inches, for instance, when given the area in square inches. The area in the sample is 20. 

```
select sqrt(20/pi());
```

This returns the value 5.046265044040321.

The following example returns the square root for COMMISSION values from the SALES table. The COMMISSION column is a DECIMAL column. This example shows how you can use the function in a query with more complex conditional logic. 

```
select sqrt(commission)
from sales where salesid < 10 order by salesid;

sqrt
------------------
10.4498803820905
3.37638860322683
7.24568837309472
5.1234753829798
...
```

The following query returns the rounded square root for the same set of COMMISSION values. 

```
select salesid, commission, round(sqrt(commission))
from sales where salesid < 10 order by salesid;

salesid | commission | round
--------+------------+-------
      1 |     109.20 |    10
      2 |      11.40 |     3
      3 |      52.50 |     7
      4 |      26.25 |     5
...
```

For more information about sample data in AWS Clean Rooms, see [Sample database](https://docs.aws.amazon.com/redshift/latest/dg/c_sampledb.html).

# TRUNC function
<a name="TRUNC"></a>

The TRUNC function truncates numbers to the previous integer or decimal. 

The TRUNC function can optionally include a second argument as an integer to indicate the number of decimal places for rounding, in either direction. When you don't provide the second argument, the function rounds to the nearest whole number. When the second argument *>n*is specified, the function rounds to the nearest number with *>n* decimal places of precision. This function also truncates a timestamp and returns a date.

## Syntax
<a name="TRUNC-synopsis"></a>

```
TRUNC (number [ , integer ] |
timestamp )
```

## Arguments
<a name="TRUNC-arguments"></a>

 *number*   
A number or expression that evaluates to a number. It can be the DECIMAL or FLOAT8 type. AWS Clean Rooms can convert other data types per the implicit conversion rules. 

 *integer* (optional)   
An integer that indicates the number of decimal places of precision, in either direction. If no integer is provided, the number is truncated as a whole number; if an integer is specified, the number is truncated to the specified decimal place. 

 *timestamp*   
The function can also return the date from a timestamp. (To return a timestamp value with `00:00:00` as the time, cast the function result to a timestamp.) 

## Return type
<a name="TRUNC-return-type"></a>

TRUNC returns the same data type as the first input argument. For timestamps, TRUNC returns a date. 

## Examples
<a name="TRUNC-examples"></a>

Truncate the commission paid for a given sales transaction. 

```
select commission, trunc(commission)
from sales where salesid=784;

commission | trunc
-----------+-------
    111.15 |   111

(1 row)
```

Truncate the same commission value to the first decimal place. 

```
select commission, trunc(commission,1)
from sales where salesid=784;

commission | trunc
-----------+-------
    111.15 | 111.1

(1 row)
```

Truncate the commission with a negative value for the second argument; `111.15` is rounded down to `110`. 

```
select commission, trunc(commission,-1)
from sales where salesid=784;

commission | trunc
-----------+-------
    111.15 |   110
(1 row)
```

Return the date portion from the result of the SYSDATE function (which returns a timestamp): 

```
select sysdate;

timestamp
----------------------------
2011-07-21 10:32:38.248109
(1 row)

select trunc(sysdate);

trunc
------------
2011-07-21
(1 row)
```

Apply the TRUNC function to a TIMESTAMP column. The return type is a date. 

```
select trunc(starttime) from event
order by eventid limit 1;

trunc
------------
2008-01-25
(1 row)
```