mean aggregate function (Databricks SQL)

Returns the mean calculated from values of a group.

Syntax

mean ( [ALL | DISTINCT] expr ) [FILTER ( WHERE cond ) ]

Arguments

  • expr: An expression that evaluates to a numeric.

  • cond: An optional boolean expression filtering the rows used for aggregation.

Returns

The result type is computed as follows:

If the argument is a DECIMAL(p, s) then the result type is a DECIMAL(p + 4, s + 4). If the maximum precision for DECIMAL is reached the increase in scale will be limited to avoid loss of significant digits. In all other cases a DOUBLE.

Nulls within the group are ignored. If a group is empty or consists only of nulls the result is NULL.

If DISTINCT is specified the mean is computed after duplicates have been removed.

This function is a synonym for avg aggregate function (Databricks SQL).

Examples

> SELECT mean(col) FROM VALUES (1), (2), (3) AS tab(col);
 2.0

> SELECT mean(DISTINCT col) FROM VALUES (1), (1), (2), (NULL) AS tab(col);
 1.5

> SELECT mean(col) FROM VALUES (1), (2), (NULL) AS tab(col);
 1.5