SQL Average Function

This article will teach you how to determine the average of multiple fields within a column in Standard SQL.

SQL AVG Function

In Standard SQL, you can calculate the average input values using the AVG function. The function syntax is as shown below:

AVG(
[DISTINCT]
expression
)
[OVER (...)]

The function takes non-NULL values and returns their average. If the input values contain a NaN, the function returns NaN.

The following are the return types for the specified input types:

Note that if you input int64 types to the avg function, it will return a float64 value.

This is an example of how to use the AVG Function.

SELECT AVG(x) AS average
FROM UNNEST([10,20,30,40,50,60]) AS x;

The example above uses the avg function to determine the average values within an array.

The resulting value is:

The function will return NaN if any specified values are of NaN type. An example is shown below:

SELECT AVG(x) AS avg
FROM UNNEST(['NaN']) AS x;

It should return NaN. However, keep in mind that some database engines may return an error.

We can calculate the average of records within a column using the AVG function. Consider the example table shown below:

If we want to calculate the average of the records in the amount’s table, we can use the query as shown below:

SELECT AVG(amount) FROM payment;

It should return the average of the records in the amounts column. Example output is as shown below:

avg
--------------------
4.2006673312979002
(1 ROW)

You can calculate the average of various records using the GROUP BY clause as shown in the query below:

SELECT payment_id, AVG(amount)
FROM payment_table GROUP BY payment_id;

In this case, we can get the result of multiple queries using the GROUP BY clause.

This tutorial covered the AVG function in Standard SQL to determine the average of multiple input records.