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 ...] ]

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

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()
;