Aggregation functions

avg#

avg(value) calculates simple average of values

Arguments#

  • value is any numeric value.

Description#

avg(value) averages values ignoring missing data (e.g null values).

Return value#

Return value type is double.

Examples#

Average transaction amount
SELECT avg(amount) FROM transactions;
avg
22.4
Average transaction amount by payment_type
SELECT payment_type, avg(amount) FROM transactions;
cash_or_cardavg
cash22.1
card27.4
null18.02

count#

count() or count(*) - counts rows.

Arguments#

  • count does not require arguments.

Description#

count() counts rows, irrespective of underlying data.

Return value#

Return value type is long.

Examples#

  • Count of rows in the transactions table.
SELECT count() FROM transactions;
count
100
  • Count of rows in the transactions table aggregated by payment_type value.
SELECT payment_type, count() FROM transactions;
cash_or_cardcount
cash25
card70
null5
note

null values are aggregated with count().

haversine_dist_deg#

haversine_dist_deg(lat, lon, ts) - traveled distance for a series of lat/lon 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

Description#

This is an aggregatation function that calculates the traveled distance for a series of lat/lon points.

Return value#

Return value type is double.

Examples#

Calculate the aggregate traveled distance for each car_id
SELECT car_id, haversine_dist_deg(lat, lon, k)
FROM table rides

ksum#

ksum(value) - adds values using Kahan algorithm.

Arguments#

  • value is any numeric value.

Description#

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.

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));
ksum
52.79143968514029

max#

max(value) - finds the highest value.

Arguments#

  • value is any numeric value

Description#

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

Return value#

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

Examples#

Highest transaction amount
SELECT max(amount) FROM transactions;
min
55.3
Highest transaction amount by payment_type
SELECT payment_type, max(amount) FROM transactions;
cash_or_cardamount
cash31.5
card55.3
null29.2

min#

min(value) - finds the lowest value.

Arguments#

  • value is any numeric value

Description#

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

Return value#

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

Examples#

Lowest transaction amount
SELECT min(amount) FROM transactions;
min
12.5
Lowest transaction amount, by payment_type
SELECT payment_type, min(amount) FROM transactions;
cash_or_cardmin
cash12.5
card15.3
null22.2

nsum#

nsum(value) - adds values using Neumaier algorithm.

Arguments#

  • value is any numeric value.

Description#

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.

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));
nsum
49.5442334742831

nsum#

nsum(value) - adds values using Neumaier algorithm.

Arguments#

  • value is any numeric value.

Description#

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.

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));
nsum
49.5442334742831

sum#

sum(value) - adds values.

Arguments#

  • value is any numeric value.

Description#

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

Return value#

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

Examples#

Sum all quantities in the transactions table
SELECT sum(quantity) FROM transactions;
sum
100
Sum all quantities in the transactions table, aggregated by item
SELECT item, sum(quantity) FROM transactions;
itemcount
apple53
orange47

Overflow#

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

Cast as long to avoid overflow
SELECT sum(cast(a AS LONG)) FROM table;