# Aggregate Functions

SQL aggregate function expressions.

## AggregateFunction

The `AggregateFunction`

class represents an aggregate function. It includes a non-null `aggregate`

property indicating an aggregate expression. Users should not need to instantiate `AggregateFunction`

instances directly, but instead should use aggregate function methods such as `count()`

, `sum()`

, *etc*.

### basis

`AggregateFunction.basis`

The `basis`

property indicates an underlying table column that can serve as a selection target for this aggregate operation.

### label

`AggregateFunction.label`

The `label`

property provides a descriptive text label.

### distinct

`AggregateFunction.distinct()`

Returns a new AggregateFunction instance that applies the aggregation over distinct values only.

### where

`AggregateFunction.where(filter)`

Returns a new AggregateFunction instance filtered according to a Boolean-valied *filter* expression.

### window

`AggregateFunction.window()`

Returns a windowed version of this aggregate function as a new WindowFunction instance.

### partitionby

`AggregateFunction.partitionby(...expressions)`

Provide one or more *expressions* by which to partition a windowed version of this aggregate function and returns a new WindowFunction instance.

### orderby

`AggregateFunction.orderby(...expressions)`

Provide one or more *expressions* by which to sort a windowed version of this aggregate function and returns a new WindowFunction instance.

### rows

`WindowFunction.rows(expression)`

Provide a window "rows" frame specification as an array or array-valued *expression* and returns a windowed version of this aggregate function as a new WindowFunction instance. A "rows" window frame is insensitive to peer rows (those that are tied according to the orderby criteria). The frame expression should evaluate to a two-element array indicating the number of preceding or following rows. A zero value (`0`

) indicates the current row. A non-finite value (including `null`

and `undefined`

) indicates either unbounded preceding row (for the first array entry) or unbounded following rows (for the second array entry).

### range

`WindowFunction.range(expression)`

Provide a window "range" frame specification as an array or array-valued *expression* and returns a windowed version of this aggregate function as a new WindowFunction instance. A "range" window grows to include peer rows (those that are tied according to the orderby criteria). The frame expression should evaluate to a two-element array indicating the number of preceding or following rows. A zero value (`0`

) indicates the current row. A non-finite value (including `null`

and `undefined`

) indicates either unbounded preceding row (for the first array entry) or unbounded following rows (for the second array entry).

## count

`count()`

Create an aggregate function that counts the number of records.

## avg

`avg(expression)`

Create an aggregate function that calculates the average of the input *expression*.

## mad

`mad(expression)`

Create an aggregate function that calculates the median absolute deviation (MAD) of the input *expression*.

## max

`max(expression)`

Create an aggregate function that calculates the maximum of the input *expression*.

## min

`min(expression)`

Create an aggregate function that calculates the minimum of the input *expression*.

## sum

`sum(expression)`

Create an aggregate function that calculates the sum of the input *expression*.

## product

`product(expression)`

Create an aggregate function that calculates the product of the input *expression*.

## median

`median(expression)`

Create an aggregate function that calculates the average of the input *expression*.

## quantile

`quantile(expression, p)`

Create an aggregate function that calculates the *p*-th quantile of the input *expression*. For example, *p* = 0.5 computes the median, while 0.25 computes the lower inter-quartile range boundary.

## mode

`mode(expression)`

Create an aggregate function that calculates the mode of the input *expression*.

## variance

`variance(expression)`

Create an aggregate function that calculates the sample variance of the input *expression*.

## stddev

`stddev(expression)`

Create an aggregate function that calculates the sample standard deviation of the input *expression*.

## skewness

`skewness(expression)`

Create an aggregate function that calculates the skewness of the input *expression*.

## kurtosis

`kurtosis(expression)`

Create an aggregate function that calculates the kurtosis of the input *expression*.

## entropy

`entropy(expression)`

Create an aggregate function that calculates the entropy of the input *expression*.

## varPop

`varPop(expression)`

Create an aggregate function that calculates the population variance of the input *expression*.

## stddevPop

`stddevPop(expression)`

Create an aggregate function that calculates the population standard deviation of the input *expression*.

## corr

`corr(a, b)`

Create an aggregate function that calculates the correlation between the input expressions *a* and *b*.

## covarPop

`covarPop(a, b)`

Create an aggregate function that calculates the population covariance between the input expressions *a* and *b*.

## regrIntercept

`regrIntercept(y, x)`

Create an aggregate function that returns the intercept of the fitted linear regression model that predicts the target expression *y* based on the predictor expression *x*.

## regrSlope

`regrSlope(y, x)`

Create an aggregate function that returns the slope of the fitted linear regression model that predicts the target expression *y* based on the predictor expression *x*.

## regrCount

`regrCount(y, x)`

Create an aggregate function that returns the count of non-null values used to fit the linear regression model that predicts the target expression *y* based on the predictor expression *x*.

## regrR2

`regrR2(y, x)`

Create an aggregate function that returns the R^2 value of the fitted linear regression model that predicts the target expression *y* based on the predictor expression *x*.

## regrSXX

`regrSXX(y, x)`

Create an aggregate function that returns the SXX value (`regrCount(y, x) * varPop(x)`

) of the fitted linear regression model that predicts the target expression *y* based on the predictor expression *x*.

## regrSYY

`regrSYY(y, x)`

Create an aggregate function that returns the SYY value (`regrCount(y, x) * varPop(y)`

) of the fitted linear regression model that predicts the target expression *y* based on the predictor expression *x*.

## regrSXY

`regrSXY(y, x)`

Create an aggregate function that returns the SXY (`regrCount(y, x) * covarPop(y, x)`

) value of the fitted linear regression model that predicts the target expression *y* based on the predictor expression *x*.

## regrAvgX

`regrAvgX(y, x)`

Create an aggregate function that returns the average x value of the data used to fit the linear regression model that predicts the target expression *y* based on the predictor expression *x*.

## regrAvgY

`regrAvgY(y, x)`

Create an aggregate function that returns the average x value of the data used to fit the linear regression model that predicts the target expression *y* based on the predictor expression *x*.

## first

`first(expression)`

Create an aggregate function that calculates the first observed value of the input *expression*.

## last

`last(expression)`

Create an aggregate function that calculates the last observed value of the input *expression*.

## argmax

`argmax(arg, value)`

Create an aggregate function that returns the expression *arg* corresponding to the maximum value of the expression *value*.

## argmin

`argmin(arg, value)`

Create an aggregate function that returns the expression *arg* corresponding to the minimum value of the expression *value*.

## stringAgg

`stringAgg(expression)`

Create an aggregate function that returns the string concatenation of the input *expression* values.

## arrayAgg

`arrayAgg(expression)`

Create an aggregate function that returns a list of the input *expression* values.