> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# MEAN

> Returns the arithmetic mean of non-NULL numeric values.

<div className="e6-meta"><span className="e6-eyebrow e6-eyebrow--quiet">Aggregate · Window</span></div>

**Aliases:** [`avg`](/query/sql/functions/aggregate/avg)

NULLs are ignored; all-NULL groups and frames return NULL.

## Syntax

A window function keeps each input row and adds a result for that row. `PARTITION BY` groups rows with matching values; without it, all input rows form one group.

### Aggregate

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
mean([DISTINCT] value)
```

### Window

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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

| Name | Type | Required | Description |
| - | - | - | - |
| value | TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE or DECIMAL(p, s) | Yes | Numeric values to aggregate. Use finite numbers whose calculation fits the result type. See Limitations for overflow and non-finite values. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point). |

## 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

| Property | Value |
| - | - |
| Deterministic | Partial (Floating-point rounding can cause small differences in the final digits.) |
| Supports DISTINCT | Partial (Grouped aggregation only; see the window limitations below.) |
| Supports window usage | Yes |
| Supports window frames | Partial (Use the ROWS forms shown here; RANGE offsets are not supported.) |
| Exact result | Partial (Floating-point rounding can cause small differences in the result.) |

## 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

| id | k | v |
| - | - | - |
| 1 | a | 2 |
| 2 | a | 2 |
| 3 | a | 4 |
| 4 | a | NULL |
| 5 | b | 6 |
| 6 | b | 8 |
| 7 | c | NULL |

### Numeric values

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH t("value") AS (VALUES (2), (4), (6), (8)) SELECT mean("value") AS result FROM t;
```

| result |
| - |
| 5.0 |

### Aggregate the sample values

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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;
```

| result |
| - |
| 4.4 |

### One result per group, including an all-NULL group

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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;
```

| k | result |
| - | - |
| a | 2.6666666666666665 |
| b | 7.0 |
| c | NULL |

### DISTINCT removes repeated input values

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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;
```

| result |
| - |
| 5.0 |

### Window over all rows in each group

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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;
```

| id | k | result |
| - | - | - |
| 1 | a | 2.6666666666666665 |
| 2 | a | 2.6666666666666665 |
| 3 | a | 2.6666666666666665 |
| 4 | a | 2.6666666666666665 |
| 5 | b | 7.0 |
| 6 | b | 7.0 |
| 7 | c | NULL |

### Running window

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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;
```

| id | k | result |
| - | - | - |
| 1 | a | 2.0 |
| 2 | a | 2.0 |
| 3 | a | 2.6666666666666665 |
| 4 | a | 2.6666666666666665 |
| 5 | b | 6.0 |
| 6 | b | 7.0 |
| 7 | c | NULL |

### Sliding window with one preceding row

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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;
```

| id | k | result |
| - | - | - |
| 1 | a | 2.0 |
| 2 | a | 2.0 |
| 3 | a | 3.0 |
| 4 | a | 4.0 |
| 5 | b | 6.0 |
| 6 | b | 7.0 |
| 7 | c | NULL |

### Only NULL values

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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;
```

| result |
| - |
| NULL |

### Empty filtered sample

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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;
```

| result |
| - |
| NULL |

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


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.