Aggregate and mathematical functions and operators

When you enter your query in the SQL Editor or create a calculation, you use Presto SQL syntax. To perform calculations on a set of values, you may find these common functions and operators useful.

ADD (+)

To add values, use the + operator. For example, SELECT 30 + 20 returns 50.

ABS

To return the absolute value of a number, use the ABS function as ABS(number). For example,  ABS(-234.5) returns 234.5.

AVG

To average a series of values, use the AVG function. For example, to create a calculation to return the average of a field's values, AVG({1}). Or, to perform the calculation within a query:

SELECT AVG(field)
FROM source
WHERE condition

COUNT

To return the number of rows or records, use the COUNT function. For example, to create a calculation to return a count of a field's values, COUNT({1}). Or, to perform the calculation within a query:

SELECT COUNT(field)
FROM source
WHERE condition

Tip: To return the number of TRUE values, use the COUNT_IF function, which does the same as COUNT(CASE WHEN x then 1 END).

DIVIDE (/)

To add values, use the / operator. For example, SELECT 30 / 10 returns 3.

MAX

To return the largest in a series of values, use the MAX function. For example, to create a calculation to return the greatest of a field's values, MAX({1}). Or, to perform the calculation within a query:

SELECT MAX(field)
FROM source
WHERE condition;

MIN

To return the smallest in a series of values, use the MIN function. For example, to create a calculation to return the least of a field's values, MIN({1}). Or, to perform the calculation within a query:

SELECT MIN(field)
FROM source
WHERE condition

MODULO (%)

To return the remainder after the division of two values, use the % operator. For example, SELECT 17 % 5 returns 2.

MULTIPLY (*)

To multiply values, use the * operator. For example, SELECT 30 * 20 returns 600.

ROUND

To round a value to the nearest integer or to a specific number of decimal places,  use the ROUND function:

• To round to the nearest integer, use ROUND as ROUND(number). For example, ROUND(234.516) returns 235.
• To round to decimal places, use ROUND as ROUND(number, decimal). For example, ROUND(234.516, 2) returns 234.52.

Note: To remove the digits after a number's decimal point without rounding the value up or down, use the TRUNCATE function.

SUBTRACT (-)

To subtract values, use the - operator. For example, SELECT 30 - 20; returns 10.

SUM

To add a series of values together, use the SUM function. For example, to create a calculation to return the sum a field's values, SUM({1}). Or, to perform the calculation within a query:

SELECT SUM(field)
FROM source
WHERE condition;

TRUNCATE

To remove any digits after the decimal point of a number, use the TRUNCATE function as TRUNCATE(number). For example, TRUNCATE(234.516) returns 234.

Note: To round a number up or down to the nearest integer, use the ROUND function.