18. What are aggregates?

Aggregate functions are built-in functions that perform calculations on sets of values and return a single summary value. In other words, instead of listing out individual column values from each row, we can aggregate them into a single number. Some common aggregate functions are:

• sum() - Adds up all the values in a numeric column.
• count() - Counts the number of non-null values in a column.
• avg() - Calculates the average of the values in a numeric column.
• min() - Finds the smallest value in a column.
• max() - Finds the largest value in a column.

Aggregate functions can only be used in the select clause (or having clause, which we will cover later) of your query. You cannot use aggregate functions in where clauses.

Let's look at an example. Say we want to know the total amount of all gifts given in 2021. We can do this as follows:

The way this query works is that the database finds all rows from the transactions table where the year on the transaction date is 2021 and passes the specified column from those rows, amount, to the sum() function. sum() then adds up all the amount values it has received, and reports the total. Usage of the other aggregate functions is similar – put the name of the column(s) you want to summarize inside the parenthesis of the function, like avg(amount) or max(amount).

Note that you can include multiple aggregate functions in a single query, like this:

select
    sum(amount) as total_amount
    ,max(amount) as largest_gift
from transactions
where year(transaction_date) = 2023

This query will return the sum of all gift amounts in 2023, as well as the amount of the largest single gift in 2023. The coming chapters will cover the nuances and uses of each of these common aggregate functions in detail.

Exercise 1: Aggregates

Development needs some summary statistics for an upcoming benchmarking meeting. They'd like the total amount of revenue contributed and the average gift amount for gifts made to the Trees fund in 2024. Write an SQL query using aggregate functions to retrieve this data.

Exercise 2: Aggregates with dates

The min() and max() functions work on numeric values as well as dates. Write an SQL query to find the date of the first and last gift from the Direct Mail channel in 2020.