avg

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. If x is Nullable, the result type is Nullable(T), where T is the result type for the corresponding non-Nullable x (Float64 for numeric inputs); an empty input returns NULL. Float64 or Date or Date32 or DateTime or DateTime64(P) or Time or Time64(P) or Interval Examples Basic usage

Query

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

Response

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

Empty table returns NaN

Query

CREATE TABLE test (t UInt8) ENGINE = Memory;

SELECT avg(t) FROM test;

Response

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

Average of an interval

Query

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;

Response

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

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

avg

avg

​avg

avg