22. avg()

The avg() aggregate function calculates the arithmetic mean of numeric data and works similarly to the same function in Excel. Here are a few things to keep in mind about avg():

• avg() only works with numbers. You cannot avg() date or text data types.
• avg() will ignore nulls. If a numeric column contains some NULL values, those values will be completely ignored when calculating the average.
• avg() can take any valid expression that resolves to a numeric type as an input, including case statements, column references, or mathematical operations.

case inside avg()

As with sum(), you will often need to include some logic inside a single avg() call that differs from the logic used in your query overall. For example, let's say we want to calculate three values for each donor in our database:

• Overall average gift amount
• Trees fund average gift amount
• Birds fund average gift amount

We can do this as follows:

select
    account_id
    ,avg(amount) as overall_average
    ,avg(case when fund = 'Trees' then amount else null end) as trees_average
    ,avg(case when fund ='Birds' then amount else null end) as birds_average
from transactions

This query leverages the fact that avg() ignores NULL values when computing the arithmetic mean. Our case statements ensure that, for example, the trees_average column only includes amount values from Trees transactions.

Exercise 1: Basic avg()

Membership wants to know each donor's average gift amount in 2023. Write an SQL query using avg() to generate this list.

Exercise 2: Complex avg()

Membership wants to know each donor's average gift amount in 2023 along with their average gift amount in 2024 so they can see which donors have upgraded their average gift year over year. Write an SQL query using avg() to generate this list.