SQL Standard

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

1. Int64 -> Float64
2. Numeric -> Numeric
3. Bignumeric -> Bignumeric
4. Float64 -> Float64

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

## Example usage

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.

## Example Usage II

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.

## Conclusion

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