MEAN

​

Syntax

​

Aggregate

mean([DISTINCT] value)

​

Window

mean(value) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame])

• RANGE frames with a numeric or time offset are not supported. Use unique ORDER BY values for calculations that advance one row at a time. DISTINCT is not supported in these window forms.

​

Arguments

​

Aggregate and window

​

Returns

• Type: DOUBLE for non-decimal numeric inputs; DECIMAL(min(38, p+4), min(38, s+4)) for DECIMAL(p,s).
• NULL behavior: NULLs are ignored; all-NULL groups and frames return NULL.
• Empty input: Returns NULL on an empty global aggregate or empty frame.

​

Properties

​

Usage notes

• Keep calculations within the result type’s range. Do not rely on an error or NULL result when integer or decimal arithmetic overflows.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• A DECIMAL result keeps its precision and scale as described above. Depending on decimal settings, query tools may report DOUBLE instead; this does not convert the returned value.
• In the DECIMAL return formulas, p and s describe the input after type conversion. Precision is limited to 38 digits, and decimal settings can reduce the input precision before calculation.
• Use DISTINCT in grouped aggregation only. It does not provide duplicate removal in a window call.

​

Examples

​

Sample data

​

Numeric values

WITH t("value") AS (VALUES (2), (4), (6), (8)) SELECT mean("value") AS result FROM t;

​

Aggregate the sample values

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT mean(v) AS result FROM sample;

​

One result per group, including an all-NULL group

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT k, mean(v) AS result
FROM sample
GROUP BY k
ORDER BY k NULLS LAST;

​

DISTINCT removes repeated input values

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT mean(DISTINCT v) AS result FROM sample;

​

Window over all rows in each group

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT id, k, mean(v) OVER (PARTITION BY k) AS result
FROM sample
ORDER BY id NULLS LAST;

​

Running window

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT id, k, mean(v) OVER (PARTITION BY k ORDER BY id NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS result
FROM sample
ORDER BY id NULLS LAST;

​

Sliding window with one preceding row

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT id, k, mean(v) OVER (PARTITION BY k ORDER BY id NULLS LAST ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS result
FROM sample
ORDER BY id NULLS LAST;

​

Only NULL values

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT mean(v) AS result FROM sample WHERE v IS NULL;

​

Empty filtered sample

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT mean(v) AS result FROM sample WHERE id < 0;

​

Limitations

• Decimal precision and scale depend on the effective input type and decimal settings. The type reported by query tools can differ from the returned value’s type.
• Floating arithmetic has no finite-value/overflow guard. NaN and infinities can be produced or propagated; no SQL error-or-NULL guarantee is made for those inputs, and client display is outside this numeric contract.
• Decimal sum state uses wrapping addition. Final decimal averaging checks multiplication and result precision and can raise Arithmetic Overflow; this does not guarantee detection of every earlier state overflow.
• With the default decimal settings used for these examples, casting a BIGINT column to DECIMAL(38, 2) produces an effective input type of DECIMAL(19, 2). Apply the return formula to p = 19 and s = 2 in that case.