Arithmetic Functions in Redshift database

Amazon Redshift database supports the below Arithmetic functions:

  • ABS
  • FLOOR
  • CEIL
  • ROUND
  • TRUNC

ABS function returns the absolute value of a number.

Examples:

SELECT ABS(-20);

abs
-----
20
SELECT ABS(100-20);

abs
-----
80

FLOOR function returns the rounded down whole number of an integer.

Example:

SELECT FLOOR(99.56);

floor
------
99

CEIL function returns the rounded up whole number of an integer.

Example:

SELECT CEIL(99.56);

ceil
-----
100

ROUND function returns the nearest rounded value of an integer. You can specify the precision for rounding.

Example:

If you specify no precision, the integer is rounded to the nearest whole number.

SELECT ROUND(99.56);

round
------
100

If you specify the precision as 1, Integer is rounded to the first decimal place.

SELECT ROUND(99.56,1);

round
------
99.6

TRUNC function truncates the integer to the previous integer. You can specify precision with TRUNC function.

Examples:

SELECT TRUNC(99.56);

trunc
------
99
SELECT TRUNC(99.56,1);

trunc
-------
99.5
See also  How to get the column count of all tables in Redshift