**Syntax to use the AVG function**

The syntax to use the Redshift AVG function to calculate the arithmetic mean is as follows:

The **DISTINCT** parameter is used when it is required to calculate the average of only unique values in a column. It is used to eliminate all repetitive values during calculating the average of the input expression.

The **ALL** parameter is used when it is required to calculate the average of all input expressions even if values are repetitive. By default, when it is not specified, the **ALL** parameter is set.

The **expression** is the targeted column in the table on which you want to apply the AVG function to get the arithmetic mean.

**Examples of using the AVG function**

In this section, we will take a few examples to fully understand how the AVG function in Redshift works. These are a few use cases examples of the Redshift AVG function:

- AVG function using
**ALL**parameter - AVG function using
**DISTINCT**parameter - AVG function using
**DEFAULT**parameter - AVG function using
**NULL**values - AVG function with WHERE statement

**AVG function using ALL parameter**

In this example, we will apply the AVG function to calculate the arithmetic mean using the **ALL** parameter on the **expenses** table as shown below. The below table includes three columns **week_number**, **week_day** and **expense_money**.

week_number |
week_day |
expense_money |

Week 01 | monday | 10 |

Week 02 | tuesday | 20 |

Week 03 | wednesday | 30 |

Week 04 | thursday | 40 |

Week 05 | friday | 50 |

Week 06 | saturday | 60 |

Week 07 | sunday | 70 |

If we manually calculate the average or arithmetic mean of the **expense_money** column from the above table, it will be 40.

In order to calculate the arithmetic, mean by using the AVG function, apply the AVG function on the third column i.e, **expense_money**.

FROM expenses

The AVG function will return the following arithmetic mean of the **expense_money** column of the **expenses** table.

40

**AVG function using the DISTINCT parameter**

In this example, we will apply the AVG function using the DISTINCT parameter on the **expense_money** column of the **expenses** table. Using the DISTINCT parameter, the AVG function will only calculate the arithmetic mean of the unique values ignoring the repetitive values.

week_number |
week_day |
expense_money |

Week 01 | Monday | 10 |

Week 02 | Tuesday | 10 |

Week 03 | Wednesday | 30 |

Week 04 | Thursday | 30 |

Week 05 | Friday | 50 |

Week 06 | Saturday | 50 |

Week 07 | Sunday | 70 |

If we manually calculate the average or arithmetic mean of the distinct values in the **expense_money** column, we will get 25.

Apply the AVG function on the third column i.e., **expense_money** to get the arithmetic mean of unique values.

FROM expenses

The AVG function will return the following arithmetic mean of the **expense_money** column of the **expenses** table.

25

**AVG function with Default parameter**

In this example, we will apply the AVG function using the default parameter on the **expense_money** column of the **expenses** table to get the arithmetic mean.

week_number |
week_day |
expense_money |

Week 01 | monday | 23 |

Week 02 | tuesday | 47 |

Week 03 | wednesday | 53 |

Week 04 | thursday | 80 |

Week 05 | Friday | 69 |

Week 06 | saturday | 21 |

Week 07 | sunday | 19 |

When we apply the AVG function on the third column i.e, **expense_money** of the **expenses** table, we will get the following result.

FROM expenses

The AVG function will return the following arithmetic mean of the **expense_money** column of the **expenses** table.

44

**AVG function with NULL values**

In this example, we will see how the AVG function works when NULL values are passed to it as input expression. The AVG function does not consider the null values during calculation and calculates the arithmetic mean of remaining values. Let’s take the example using the null values in the **expenses** table as shown below.

week_number |
week_day |
expense_money |

Week 01 | monday | 23 |

Week 02 | tuesday | 47 |

Week 03 | wednesday | NULL |

Week 04 | thursday | 80 |

Week 05 | friday | NULL |

Week 06 | saturday | 21 |

Week 07 | sunday | 19 |

Apply the AVG function on the third column i.e, **expense_money** of the **expenses** table to get the arithmetic mean.

FROM expenses

**expense_money** column of the **expenses** table.

38

**AVG function with WHERE statement**

In this example, we will see how the AVG function works when you apply a **WHERE** statement in your query. The **expenseweek** table in the Redshift cluster is shown below and we will calculate the arithmetic mean or average of the **expense_money** for **week 01** only.

weeknumber |
weekday |
expense_money |

Week 01 | monday | 23 |

Week 01 | tuesday | 47 |

Week 01 | wednesday | 57 |

Week 01 | thursday | 80 |

Week 01 | friday | 17 |

Week 01 | saturday | 21 |

Week 01 | sunday | 19 |

Week 02 | monday | 123 |

Week 02 | tuesday | 97 |

Week 02 | wednesday | 89 |

Now, in order to calculate the arithmetic, mean of the **expense_money** for only Week 01, we will apply a WHERE statement along with the AVG function.

Apply the AVG function on the third column i.e, **expense_money** of the **expenseweek** using the WHERE statement to get the arithmetic mean of only Week 01.

FROM weekexpense

Where weeknumber= 'Week 01'

The AVG function will return the following arithmetic mean of the **expense_money** column of the **expenseweek** table.

37

**Conclusion**

In this article, we have discussed how to use the AVG function in Redshift to calculate the arithmetic mean or average of numeric expressions. The AVG function can be used with the ALL or DISTINCT option to calculate the arithmetic mean of unique or all input expressions. It can also be used with the WHERE statement to get the arithmetic mean of specific entries from the column.