Aggregate Functions

Aggregate functions are calculated for a bounded series of values, otherwise known as WINDOWS.

Aggregate functions can be used in SELECT and WHEN clauses of QUERIES only.

The following aggregate functions are available:

Function	Data Types	Description
avg	NUMBER	Average. sum divided by count
count	NUMBER, STRING	Quantity of values in the window
count_distinct	NUMBER, STRING	Quantity of unique values
first	NUMBER, STRING	The oldest value in the window
last	NUMBER, STRING	The most recent value in the window
max	NUMBER, STRING	The highest number, or last alphabetically
median	NUMBER	The middle of values. If count is even, the middle point between two middle values
min	NUMBER, STRING	The lowest number, or first alphabetically
mode	NUMBER, STRING	The most commonly repeated value
previous	NUMBER	The value before the most recent value
slope	NUMBER	The regresion line slope of values
stddev	NUMBER	Standard deviation
stddev_pop	NUMBER	Standard Deviation of population
sum	NUMBER	The sum of all values.
variance	NUMBER	Variance
variance_pop	NUMBER	Variance of population

Syntax

The aggregate function is prefixed by a window’s name or alias, and a period:

<window name>.<function>

<window alias>.<function>

Example

In the SELECT clause:

SELECT last_100_bids.AVG, last_100_bids.AVG
FROM last_100_bids
WHEN ...
OUTPUT (...);

In the SELECT clause with alias:

SELECT a.AVG, g.AVG
FROM last_100_bids_on_apple a, 
     last_100_bids_on_google g
WHEN ...
OUTPUT (...);

In the WHEN clause:

SELECT ...
FROM last_100_bids
WHEN last_100_bids.AVG > last_100_bids.AVG
OUTPUT (...);

In the WHEN clause with alias:

SELECT ...
FROM last_100_bids_on_apple a, 
     last_100_bids_on_google g
WHEN a.AVG > g.AVG
OUTPUT (...);