# Postgres avg() function

Calculate the average value of a set of numbers

The Postgres `avg()`

function calculates the arithmetic mean of a set of numeric values.

This function is particularly useful when you need to understand typical values in a dataset, compare different groups, or identify trends over time. For example, you might use it to calculate the average order value for an e-commerce platform, the average response time for a web service, or the mean of sensor readings over time.

## Function signature

The `avg()`

function has the simple form:

`expression`

: Any numeric expression or column name whose average you want to calculate.

The `avg()`

function returns an output of the type `numeric`

when applied to integer or numeric values. When used with floating-point values, the output type is `double precision`

.

## Example usage

Consider a table `weather_data`

tracking the temperature readings for different cities. It has the columns `date`

, `city`

and `temperature`

. We will use the `avg()`

function to analyze this data.

### Calculating the average temperature

To calculate the average temperature reading across all cities and dates, you can use the following query:

This query computes the average of all values in the `temperature`

column.

### Calculating the average temperature by city

You can use `avg()`

with a `GROUP BY`

clause to calculate averages for different cities:

This query returns the average temperature recorded for each city, ordered by the highest average temperature:

## Advanced examples

### Using avg() with a FILTER clause

Postgres allows you to use a `FILTER`

clause with aggregate functions to selectively include rows in the calculation:

This query calculates the average temperature for each city and the average temperature since March 3rd, 2024.

### Using avg() in a subquery

You can use `avg()`

in a subquery to compare individual values against the average:

This query calculates the difference between each temperature reading and the overall average temperature, and returns the top 5 records with the largest deviations:

### Calculating a moving average

We can use `avg()`

as a window function to calculate a moving average over the specified window of rows.

This query calculates a 3-day moving average of temperature readings for each city, alongside the current temperature:

## Additional considerations

### Handling NULL values

The `avg()`

function automatically ignores NULL values in its calculations. If all values are NULL, it returns NULL.

### Precision and rounding

The `avg()`

function returns a numeric value with the maximum precision and scale of any argument. You may want to use the `round()`

function to control the number of decimal places in the result:

### Performance implications

When working with large datasets, calculating averages can be resource-intensive, especially when combined with complex `GROUP BY`

clauses or subqueries. Consider using materialized views or pre-aggregating data for frequently used averages for analytics applications.

## Alternative functions

`percentile_cont()`

: Calculates a continuous percentile value. It can be used to compute the median or other percentiles. Note that it is an ordered-set aggregate function and requires a`WITHIN GROUP`

clause.`mode()`

: Returns the most frequent value in a set. It is also an ordered-set aggregate function.

## Resources

Last updated on