Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
avg
aggregate functionApplies to: Databricks SQL Databricks Runtime
Returns the mean calculated from values of a group. This function is a synonym for mean aggregate function.
avg( [ALL | DISTINCT] expr) [FILTER ( WHERE cond ) ]
This function can also be invoked as a window function using the OVER
clause.
expr
: An expression that evaluates to a numeric or an interval.cond
: An optional boolean expression filtering the rows used for aggregation.The result type is computed as for the arguments:
DECIMAL(p, s)
: The result type is a DECIMAL(p + 4, s + 4)
. If the maximum precision for DECIMAL is reached the increase in scale will be limited to avoid loss of significant digits.INTERVAL YEAR TO MONTH
.INTERVAL DAY TO SECOND
.Nulls within the group are ignored. If a group is empty or consists only of nulls, the result is NULL.
If DISTINCT
is specified the average is computed after duplicates have been removed.
If the result overflows the result type, Azure Databricks raises an ARITHMETIC_OVERFLOW error. To return a NULL instead use try_avg.
Warning
In Databricks Runtime, if spark.sql.ansi.enabled is false
, an overflow returns NULL
instead of an error.
> SELECT avg(col) FROM VALUES (1), (2), (3) AS tab(col);
2.0
> SELECT avg(DISTINCT col) FROM VALUES (1), (1), (2) AS tab(col);
1.5
> SELECT avg(col) FROM VALUES (1), (2), (NULL) AS tab(col);
1.5
> SELECT avg(col) FROM VALUES (INTERVAL '1' YEAR), (INTERVAL '2' YEAR) AS tab(col);
1-6
-- Overflow results in NULL for try_avg()
> SELECT try_avg(col) FROM VALUES (5e37::DECIMAL(38, 0)), (5e37::DECIMAL(38, 0)) AS tab(col);
NULL
-- Overflow causes error for avg() in ANSI mode.
> SELECT avg(col) FROM VALUES (5e37::DECIMAL(38, 0)), (5e37::DECIMAL(38, 0)) AS tab(col);
Error: CANNOT_CHANGE_DECIMAL_PRECISION
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayTraining
Module
Use built-in functions and GROUP BY in Transact-SQL - Training
Use built-in functions and GROUP BY in Transact-SQL
Documentation
sum aggregate function - Azure Databricks - Databricks SQL
Learn the syntax of the sum aggregate function of the SQL language in Databricks SQL and Databricks Runtime.
count aggregate function - Azure Databricks - Databricks SQL
Learn the syntax of the count aggregate function of the SQL language in Databricks SQL and Databricks Runtime.
nullif function - Azure Databricks - Databricks SQL
Learn the syntax of the nullif function of the SQL language in Databricks SQL and Databricks Runtime.