Links

Mathematical operators

Mathematical operators

Operator
Description
+
Addition
-
*
/
Division (integer division performs truncation)
_/
%
Modulus (remainder)
^
Power

Addition

Sums up two numbers.

Examples

SQL

CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
1 + 1 AS add_example1,
1 + 2 + 3 AS add_example2,
1.1 + 2.2 AS add_example3
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;

Query result

add_example1
add_example2
add_example3
2
6
3.3000000000000003

Subtraction

Subtracts one number from another.

Examples

SQL

CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
1 - 1 AS sub_example1,
1 - 2 - 3 AS sub_example2,
2.2 - 1.1 AS sub_example3
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;

Query result

sub_example1
sub_example2
sub_example3
0
-4
1.1

Negation

Negates the value of a number.

Examples

SQL

CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
- 1 AS neg_example1,
- (- 1) AS neg_example2,
- 0 AS neg_example3
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;

Query result

neg_example1
neg_example2
neg_example3
-1
1
0

Multiplication

Multiplies two numbers.

Examples

SQL

CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
1 * 0 AS mult_example1,
1 * 2 * 3 AS mult_example2,
1.1 * 2.2 AS mult_example3
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;

Query result

mult_example1
mult_example2
mult_example3
0
6
2.4200000000000004

Division

Divides one number with another.
Note that when dividing integers, the result is truncated, meaning that only the integer portion of the result is returned if the numbers do not divide evenly.

Examples

SQL

CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
4 / 2 AS div_example1,
4 / 2 / 2 AS div_example2,
3 / 4 AS div_example3,
2.2 / 3.3 AS div_example4
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;

Query result

div_example1
div_example2
div_example3
div_example4
2
1
0
0.6666666666666667

Integer division

Divides one integer from another.
Inputs that are not integers are truncated and then divided.

Examples

SQL

CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
4 _/ 3 as div_example1,
4.1 _/ 3.1 as div_example2,
4.1 / 3.1 as div_example3,
4.8 _/ 3.8 as div_example4,
4.8 / 3.8 as div_example5,
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;

Query result

div_example1
div_example2
div_example3
div_example4
div_example5
1
1
1.3225,806451612903
1
1.263157894736842

Modulus

Computes the modulus (remainder) from dividing one number with another.

Examples

SQL

CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
4 % 3 AS mod_example1,
10 % 4 % 2 AS mod_example2,
5.5 % 2.2 AS mod_example3
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;

Query result

mod_example1
mod_example2
mod_example3
1
0
1.0999999999999996

Power

Raises one number to the power of another.
This operator has the same functionality as POW and POWER.

SQL

CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
2 ^ 2 AS pow_example1,
3.3 ^ 1.1 AS pow_example2
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;

Query result

pow_example1
pow_example2
4
3.718479005997499

Example with sample data

The following example uses Upsolver sample data that is available to you through SQLake.

SQL

CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
orderid AS order_id,
ROUND(nettotal / (1 + taxrate), 2) AS pretax_total,
taxrate,
nettotal
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 3;

Query result

order_id
pretax_total
taxrate
nettotal
NzwdlehpJ9
349.98
0.12
391.98
6nDo5vqL3B
741.95
0.12
830.98
mqEu3Y96pE
1157.74
0.12
1296.67