avg | ClickHouse Docs

Introduced in: v1.1.0

Calculates the arithmetic mean.

Syntax

avg(x)

Arguments

x — Input values. (U)Int* or Float* or Decimal or Date or Date32 or DateTime or DateTime64(P) or Time or Time64(P) or Interval

Returned value

Returns the arithmetic mean. For a numeric x the result is a Float64, and NaN if x is empty. For a date, time or interval x the result keeps the type of x: the mean is rounded to the nearest representable value, and an empty x gives the zero value of that type. Float64 or Date or Date32 or DateTime or DateTime64(P) or Time or Time64(P) or Interval

Examples

Basic usage

SELECT avg(x) FROM VALUES('x Int8', 0, 1, 2, 3, 4, 5);

┌─avg(x)─┐
│    2.5 │
└────────┘

Empty table returns NaN

CREATE TABLE test (t UInt8) ENGINE = Memory;

SELECT avg(t) FROM test;

┌─avg(t)─┐
│    nan │
└────────┘

Average of an interval

CREATE TABLE requests (req_id Int64, duration IntervalMillisecond) ENGINE = Memory;

INSERT INTO requests VALUES (1, 100), (2, 200), (3, 300);

SELECT avg(duration) AS avg_duration, toTypeName(avg_duration) FROM requests;

┌─avg_duration─┬─toTypeName(avg_duration)─┐
│          200 │ IntervalMillisecond      │
└──────────────┴──────────────────────────┘