SQL Aggregate Functions – With Example Data Queries for Beginners

What is an Aggregate Function?

SQL aggregate functions will seem very familiar if you have worked with spreadsheets.

Have you ever used SUM in Google Sheets or Excel? The SUM function exists in SQL as well, and is called an aggregate function.

Aggregate functions do a particular task across database rows. For example, say you run a yearly fundraiser. You have a database of donors along with the amount they donated each year.

You might use the COUNT function to determine how many donations were received, or the SUM function to find out how much you have made in total this year.

We will use the following small data set for illustrative purposes.

In this article, we will cover the following aggregate functions: COUNT, SUM, MIN/MAX, and AVG.

The COUNT function

The COUNT function returns a count of rows. In its simplest form, COUNT counts the total number of rows in your table.

To get that value from our donor table, you would run the following query: SELECT COUNT(*) FROM donors. This will return the total number of donors, which in this case is 6. Here, of course, you could just count the rows in this example, but let's imagine there are a lot more rows.

You might want to count only certain rows, however. In our donor database example, say you want to count the number of donors who have an email listed.

If you run the query SELECT COUNT(email) FROM donors, you will get 4, which is the total number of donors with non-null values in the email column.

Keep in mind that unless you use an alias, the column returned will simply be labeled "count". If you want a more descriptive name, run a query using AS to create an alias, for example SELECT COUNT(email) FROM donors AS email_count.

The SUM function

SUM is a super handy aggregate function you can use to add together numerical values from different rows.

In our donor database, you could use SUM to add up all of the 2021 donations by running the query SELECT SUM(donation_2021) FROM donors. Note that SUM ignores NULL values, so the result of this query will be 7175.

Remember that SUM, like other aggregate functions, works across rows, not columns.

So in our example, you could use it to add together everyone’s donations from 2021 (represented by the donation_2021 column), but not to add together all of one person's donations from the donation_2020 column and the donation_2021 column.

The MIN and MAX functions

As you might guess, you can use MIN and MAX to find the minimum and maximum values in a particular column of a database.

In our example data, imagine you want to find the minimum and maximum donation amounts for 2021. You could do this by running the query: SELECT MIN(donation_2021) AS "Minimum donation 2021", MAX(donation_2021) AS "Maximum donation 2021" FROM donors.

Note that in this example, we are assigning aliases to the returned columns using quotation marks. Quotation marks are not necessary if your alias has no spaces in it, but we are using quotes here so we can use spaces.

An interesting side note: you can use MIN and MAX on non-numeric values. MIN will find the smallest number, the closest letter to A, or the earliest date. MAX will find the largest number, the closest letter to Z, or the latest date. Very handy!

The AVG function

Finally, the AVG function averages numeric values from a particular column. As with SUM, it ignores NULL values.

To get an average of all of the donations from 2020, you can run the following query: SELECT AVG(donation_2020) FROM donors. The result of this query would be 1435.

Wrapping Up

As you have seen, aggregate functions are easy and useful tools for analyzing data in SQL. AVG, MIN/MAX, SUM , and COUNT are an important addition to your SQL skillset.