20. sum()

The sum() aggregate functions adds numbers together. It is very similar, conceptually, to the sum() function in Excel. We've already seen some simple examples of sum() but let's summarize a few key points about this function:

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

case inside sum()

Often, you will want to use some set of logic in your where clause (e.g., to identify a subset of all transactions) and then use some other piece of logic to get a sum() of some number for only a further subset of those rows. For example, let's say we want a list of all donors who gave at least once in 2024 that includes:

• The donor's ID
• The donor's total 2023 revenue
• The donor's total 2023 Birds fund revenue

We can accomplish this in a single query as follows:

select
    account_id
    ,sum(amount) as total_2024_revenue
    ,sum(
        case
            when fund = 'Birds' then Amount
            else 0
        end
        ) as birds_2024_revenue
from transactions
where year(transaction_date) = 2024
group by account_id

This query uses the where clause to get down to the set of transactions in 2024. But since we want our Birds fund sum to only include revenue to the Birds fund (not the Trees fund), we use a case statement inside the sum() function. This case statement tells the database, "If the fund is Birds, then include the value from the Amount column in the sum. But if the fund is not Birds, then use 0 instead." As a result, only transactions with fund = 'Birds' will have their amounts added up in our birds_2024_revenue column.

In contrast, our total_2024_revenue column sums up all amount values from all rows that are returned by our from and where clauses. So it will be the total revenue regardless of fund.

Exercise 1: Basic sum()

Direct Marketing is looking to understand which channels have the highest volume of solicitations. Using the source codes table, write a query to show the sum of quantity by source code channel.

Exercise 2: Advanced sum()

Membership is looking for a summary of each donor's giving in 2023 and 2024 for their tax statement mailing. Write an SQL query that shows the following totals for each account ID:

• Total 2024 revenue
• Total 2024 Birds Fund revenue
• Total 2024 Trees Fund Revenue
• Total 2023 revenue

Exercise 3: sum() with exists

Your last output was great, but Membership wants to restrict the output to only those donors who gave at least one gift in 2024. Update your query using exists to enforce this constraint.