AVG() GROUP BY ...
All functions > GROUP BY > AVG() GROUP BY ...
Returns the average of the values in the group.
Syntax
AVG(expr) [ FILTER (WHERE condition) ] [ GROUP BY feature [, feature ...] ]
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
-- Average of values
f1 := ZIP(ARRAY(1, 2, 3, 4) AS value).TRANSFORM(SELECT AVG(value)).UNWRAP_ONE(),
-- Average of values excluding NULLs
f2 := ZIP(ARRAY(1, NULL::BIGINT, 3, NULL::BIGINT) AS value).TRANSFORM(
SELECT AVG(value)
).UNWRAP_ONE(),
-- Average of DATE values
f3 := ZIP(
ARRAY(DATE '2024-01-01', DATE '2024-01-02', DATE '2024-01-03') AS d
).TRANSFORM(SELECT AVG(d)).UNWRAP_ONE(),
-- Average of TIMESTAMP values
f4 := ZIP(
ARRAY(
TIMESTAMP '2024-01-01 10:00:00',
TIMESTAMP '2024-01-02 10:00:00',
TIMESTAMP '2024-01-03 10:00:00'
) AS t
).TRANSFORM(SELECT AVG(t)).UNWRAP_ONE()
;Result
| f1 VARCHAR | f2 VARCHAR | f3 TIMESTAMP | f4 TIMESTAMP |
|---|---|---|---|
| 2.5 | 2.0 | 2024-01-02T00:00:00 | 2024-01-02T10:00:00 |