This page describes the available functions to assist with performing aggregate calculations.

avg

avg(value) calculates simple average of values ignoring missing data (e.g null values).

Arguments:

• value is any numeric value.

Return value:

Return value type is double.

Examples:

SELECT avg(amount) FROM transactions;

SELECT payment_type, avg(amount) FROM transactions;

count

count() or count(*) - counts rows irrespective of underlying data.

Arguments:

• count does not require arguments.

Return value:

Return value type is long.

Examples:

• Count of rows in the transactions table.

SELECT count() FROM transactions;

• Count of rows in the transactions table aggregated by payment_type value.

SELECT payment_type, count() FROM transactions;

:::note

null values are aggregated with count().

:::

count_distinct

count_distinct(STRING_COL) or count_distinct(SYMBOL_COL) - counts distinct values in STRING or SYMBOL columns.

Return value:

Return value type is long.

Examples:

• Count of distinct sides in the transactions table. Side column can either be BUY or SELL or null

SELECT count_distinct(side) FROM transactions;

• Count of distinct counterparties in the transactions table aggregated by payment_type value.

SELECT payment_type, count_distinct(counterparty) FROM transactions;

:::note

null values are not counted in ``count_distinct` functions.

:::

haversine_dist_deg

haversine_dist_deg(lat, lon, ts) - calculates the traveled distance for a series of latitude and longitude points.

Arguments:

• lat is the latitude expressed as degrees in decimal format (double)
• lon is the longitude expressed as degrees in decimal format (double)
• ts is the timestamp for the data point

Return value:

Return value type is double.

Examples:

SELECT car_id, haversine_dist_deg(lat, lon, k)
  FROM table rides

ksum

ksum(value) - adds values ignoring missing data (e.g null values). Values are added using the

Kahan compensated sum algorithm. This is only beneficial for floating-point values such as float or double.

Arguments:

• value is any numeric value.

Return value:

Return value type is the same as the type of the argument.

Examples:

SELECT ksum(a)
FROM (SELECT rnd_double() a FROM long_sequence(100));

max

max(value) - returns the highest value ignoring missing data (e.g null values).

Arguments:

• value is any numeric value

Return value:

Return value type is the same as the type of the argument.

Examples:

SELECT max(amount) FROM transactions;

SELECT payment_type, max(amount) FROM transactions;

min

min(value) - returns the lowest value ignoring missing data (e.g null values).

Arguments:

• value is any numeric value

Return value:

Return value type is the same as the type of the argument.

Examples:

SELECT min(amount) FROM transactions;

SELECT payment_type, min(amount) FROM transactions;

nsum

nsum(value) - adds values ignoring missing data (e.g null values). Values are added using the Neumaier sum algorithm. This is only beneficial for floating-point values such as float or double.

Arguments:

• value is any numeric value.

Return value:

Return value type is the same as the type of the argument.

Examples:

SELECT nsum(a)
FROM (SELECT rnd_double() a FROM long_sequence(100));

sum

sum(value) - adds values ignoring missing data (e.g null values).

Arguments:

• value is any numeric value.

Return value:

Return value type is the same as the type of the argument.

Examples:

SELECT sum(quantity) FROM transactions;

SELECT item, sum(quantity) FROM transactions;

Overflow

sum does not perform overflow check. To avoid overflow, you can cast the argument to wider type.

SELECT sum(cast(a AS LONG)) FROM table;