AVG() GROUP BY ...
All functions > GROUP BY > AVG() GROUP BY ...
Returns the average of the values in the group.
Syntax
Diagram(
Sequence(
Terminal("AVG"),
Terminal("("),
NonTerminal("expr"),
Terminal(")"),
Choice(0, Skip(),
Sequence(
Terminal("FILTER"),
Terminal("("),
Terminal("WHERE"),
NonTerminal("condition"),
Terminal(")")
)
),
Choice(0, Skip(),
Sequence(
Terminal("GROUP BY"),
OneOrMore(NonTerminal("feature"), Terminal(","))
)
)
)
)| Parameter | Type | Required | Description |
|---|---|---|---|
expr | T | Yes | The numeric expression to average |
condition | BOOLEAN | No | The condition to filter the values before aggregation |
feature | FEATURE | No | The features to group by (many features are supported) |
With:
T: Any numeric type
Notes
- Calculates the arithmetic mean (sum divided by count)
- NULL values are excluded from the calculation
- Returns NULL if all values are NULL or group is empty
- Result type matches input type
- Can be used with WHERE clause to filter before aggregation
- Can be used with GROUP BY clause for grouped aggregation
Examples
FeatureQL
SELECT
f1 := ZIP(ARRAY[1, 2, 3, 4] AS value).TRANSFORM(SELECT AVG(value)).UNWRAP_ONE(), -- Average of values
f2 := ZIP(ARRAY[1, NULL::BIGINT, 3, NULL::BIGINT] AS value).TRANSFORM(SELECT AVG(value)).UNWRAP_ONE() -- Average of values excluding NULLs
;Result
| f1 VARCHAR | f2 VARCHAR |
|---|---|
| 2.5 | 2.0 |